package com.itr.kbm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.itr.kbm.dao.EmployeeDao;
import com.itr.kbm.dao.FileStatusDao;
import com.itr.kbm.dao.ListAdminDao;
import com.itr.kbm.dao.ListKopegDao;
import com.itr.kbm.dao.ListKoptelProductDao;
import com.itr.kbm.model.Employee;
import com.itr.kbm.model.FileStatus;
import com.itr.kbm.model.FinalReport;
import com.itr.kbm.model.FinalReportProduct;
import com.itr.kbm.model.Kopeg;
import com.itr.kbm.model.KoptelProduct;
import com.itr.kbm.model.ListAdmin;
import com.itr.kbm.model.ListKopeg;
import com.itr.kbm.model.ListKoptelProduct;
import com.itr.kbm.service.ExcelService;

@Service("excelService")
@Transactional(readOnly = false)
public class ExcelServiceImpl implements ExcelService {

	@Autowired
	public EmployeeDao employeeDao;

	@Autowired
	public ListKopegDao listKopegDao;

	@Autowired
	public FileStatusDao fileStatusDao;

	public List<String> fileList;
	public List<Kopeg> listAllCompare;
	public List<Kopeg> listDifference;

	public List<FinalReport> listFinalReport;

	@Autowired
	public ListKoptelProductDao listKoptelProductDao;

	@Autowired
	public ListAdminDao listAdminDao;

	public List<KoptelProduct> listAllCompareProductAngsuranTetap;
	public List<KoptelProduct> listAllCompareProductAngsuranLainLain;
	public List<KoptelProduct> listDifferenceProductAngsuranTetap;
	public List<KoptelProduct> listDifferenceProductAngsuranLainLain;

	public List<FinalReportProduct> listFinalReportProduct;

	public boolean checkInListAdmin(String username, String password) {
		return listAdminDao.checkInListAdmin(username, password);
	}

	public boolean checkInListKopeg(String username, String password) {
		return listKopegDao.checkInListKopeg(username, password);
	}

	public boolean checkInListKoptelProduct(String username, String password) {
		return listKoptelProductDao
				.checkInListKoptelProduct(username, password);
	}

	public boolean checkInListAdmin(String username) {
		return listAdminDao.checkInListAdmin(username);
	}

	public boolean checkInListKopeg(String username) {
		return listKopegDao.checkInListKopeg(username);
	}

	public boolean checkInListKoptelProduct(String username) {
		return listKoptelProductDao.checkInListKoptelProduct(username);
	}

	public void resetPasswordAdmin(String username) {
		ListAdmin admin = listAdminDao.resetPasswordAdmin(username);

		admin.setPassword("123456");
		listAdminDao.saveOrUpdate(admin);
	}

	public void resetPasswordKopeg(String username) {
		ListKopeg kopeg = listKopegDao.resetPasswordKopeg(username);

		kopeg.setPassword("123456");
		listKopegDao.saveOrUpdate(kopeg);
	}

	public void resetPasswordKoptelProduct(String username) {
		ListKoptelProduct koptelProduct = listKoptelProductDao
				.resetPasswordKoptelProduct(username);

		koptelProduct.setPassword("123456");
		listKoptelProductDao.saveOrUpdate(koptelProduct);
	}

	public void changePasswordAdmin(String username, String password) {
		ListAdmin admin = listAdminDao.changePasswordAdmin(username, password);

		admin.setPassword(password);
		listAdminDao.saveOrUpdate(admin);
	}

	public void changePasswordKopeg(String username, String password) {
		ListKopeg kopeg = listKopegDao.changePasswordKopeg(username, password);

		kopeg.setPassword(password);
		listKopegDao.saveOrUpdate(kopeg);

	}

	public void changePasswordKoptelProduct(String username, String password) {
		ListKoptelProduct koptelProduct = listKoptelProductDao
				.changePasswordKoptelProduct(username, password);

		koptelProduct.setPassword(password);
		listKoptelProductDao.saveOrUpdate(koptelProduct);
	}

	public void saveExcel(MultipartFile file, String path) {

		File files = new File(path);

		try {
			file.transferTo(files);
		} catch (IllegalStateException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	// ADMIN
	public String readEmployee(MultipartFile file, String path,
			String kodeKopeg, String months, String years, String user) {

		List<Employee> listEmployee = new ArrayList<Employee>();
		String re = "";

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();

				if (row.getRowNum() != 0) {

					String nik = "";
					String nama = "";
					String personalArea = "";
					String psa = "";
					String vPsa = "";
					String kodeDivisi = "";
					String kodeLoker = "";
					String loker = "";
					String kodePosisi = "";
					String posisi = "";
					Date tglMliKerja = null;
					String kerjaBantu = "";
					String band = "";
					String klas = "";
					String tmpLahir = "";
					Date tglLahir = null;
					String gender = "";
					Date tglCapeg = null;
					Date tglPensiunNorm = null;
					String alamat = "";
					String kota = "";
					String status = "";
					String agama = "";
					double gadas = 0.0;
					double perumahan = 0.0;
					double koptel = 0.0;
					double thp = 0.0;
					double sepertigaThp = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {

						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 2:
								personalArea = cell.getStringCellValue();
								break;
							case 3:
								psa = cell.getStringCellValue();
								break;
							case 4:
								vPsa = cell.getStringCellValue();
								break;
							case 5:
								kodeDivisi = cell.getStringCellValue();
								break;
							case 6:
								kodeLoker = cell.getStringCellValue();
								break;
							case 7:
								loker = cell.getStringCellValue();
								break;
							case 8:
								kodePosisi = cell.getStringCellValue();
								break;
							case 9:
								posisi = cell.getStringCellValue();
								break;
							case 10:
								tglMliKerja = cell.getDateCellValue();
								break;
							case 11:
								kerjaBantu = cell.getStringCellValue();
								break;
							case 12:
								band = cell.getStringCellValue();
								break;
							case 13:
								klas = cell.getStringCellValue();
								break;
							case 14:
								tmpLahir = cell.getStringCellValue();
								break;
							case 15:
								tglLahir = cell.getDateCellValue();
								break;
							case 16:
								gender = cell.getStringCellValue();
								break;
							case 17:
								tglCapeg = cell.getDateCellValue();
								break;
							case 18:
								tglPensiunNorm = cell.getDateCellValue();
								break;
							case 19:
								alamat = cell.getStringCellValue();
								break;
							case 20:
								kota = cell.getStringCellValue();
								break;
							case 21:
								status = cell.getStringCellValue();
								break;
							case 22:
								agama = cell.getStringCellValue();
								break;
							case 23:
								gadas = cell.getNumericCellValue();
								break;
							case 24:
								perumahan = cell.getNumericCellValue();
								break;
							case 25:
								koptel = cell.getNumericCellValue();
								break;
							case 26:
								thp = cell.getNumericCellValue();
								break;
							case 27:
								sepertigaThp = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}

					Employee employee = new Employee(nik, nama, personalArea,
							psa, vPsa, kodeDivisi, kodeLoker, loker,
							kodePosisi, posisi, tglMliKerja, kerjaBantu, band,
							klas, tmpLahir, tglLahir, gender, tglCapeg,
							tglPensiunNorm, alamat, kota, status, agama, gadas,
							perumahan, koptel, thp, sepertigaThp);
					listEmployee.add(employee);
				}
			}

			List<Employee> oldListEmployee = employeeDao.getAllEmployee();
			for (int i = 0; i < oldListEmployee.size(); i++) {
				employeeDao.delete(oldListEmployee.get(i));
			}

			for (int i = 0; i < listEmployee.size(); i++) {
				employeeDao.save(listEmployee.get(i));
			}

			String type = "uploadEmployee";
			String status = "success";
			Date date = new Date();

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			re = "SUCCESS UPLOAD DATA EMPLOYEE";

			fis.close();

		} catch (Exception e) {

			for (int i = 0; i < listEmployee.size(); i++) {
				employeeDao.delete(listEmployee.get(i));
			}

			String type = "uploadEmployee";
			String status = "failed";
			Date date = new Date();
			path = "-";

			// FAILED IF FORMAT DATA EMPLOYEE IS NOT MATCH WITH DEFAULT FORMAT
			File filed = new File(path);
			filed.delete();

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to upload. FORMAT DATA EMPLOYEE IS NOT MATCH WITH DEFAULT FORMAT.");

			re = "Failed to upload. FORMAT DATA EMPLOYEE IS NOT MATCH WITH DEFAULT FORMAT.";
		}

		return re;
	}

	public String readListKopeg(MultipartFile file, String path,
			String kodeKopeg, String months, String years, String user) {

		List<ListKopeg> listListKopeg = new ArrayList<ListKopeg>();
		String re = "";

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();

				int id = 0;
				String kodeKopegs = "";
				String namaKopeg = "";

				Iterator<Cell> cellIterator = row.cellIterator();

				while (cellIterator.hasNext()) {

					Cell cell = cellIterator.next();

					try {
						switch (cell.getColumnIndex()) {
						case 0:
							id = (int) cell.getNumericCellValue();
							break;
						case 1:
							kodeKopegs = cell.getStringCellValue();
							break;
						case 2:
							namaKopeg = cell.getStringCellValue();
							break;
						}
					} catch (Exception e) {
					}

				}

				ListKopeg listKopeg = new ListKopeg(id, kodeKopegs, namaKopeg,
						"123456");
				listListKopeg.add(listKopeg);

			}

			List<ListKopeg> oldListKopeg = listKopegDao.getAllListKopeg();
			for (int i = 0; i < oldListKopeg.size(); i++) {
				listKopegDao.delete(oldListKopeg.get(i));
			}

			for (int i = 0; i < listListKopeg.size(); i++) {
				listKopegDao.save(listListKopeg.get(i));
			}

			String type = "uploadListKopeg";
			String status = "success";
			Date date = new Date();

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			re = "SUCCESS UPLOAD DATA LIST KOPEG";

			fis.close();

		} catch (Exception e) {

			for (int i = 0; i < listListKopeg.size(); i++) {
				listKopegDao.delete(listListKopeg.get(i));
			}

			String type = "uploadListKopeg";
			String status = "failed";
			Date date = new Date();

			// FAILED IF FORMAT DATA LIST KOPEG IS NOT MATCH WITH DEFAULT FORMAT
			File filed = new File(path);
			filed.delete();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to upload. FORMAT DATA LIST KOPEG IS NOT MATCH WITH DEFAULT FORMAT.");

			re = "Failed to upload. FORMAT DATA LIST KOPEG IS NOT MATCH WITH DEFAULT FORMAT.";
		}

		return re;
	}

	public String readListKoptelProduct(MultipartFile file, String path,
			String kodeKoptelProduct, String months, String years, String user) {

		List<ListKoptelProduct> listListKoptelProduct = new ArrayList<ListKoptelProduct>();
		String re = "";

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();

				int id = 0;
				String kodeKoptelProducts = "";
				String namaKoptelProduct = "";

				Iterator<Cell> cellIterator = row.cellIterator();

				while (cellIterator.hasNext()) {

					Cell cell = cellIterator.next();

					try {
						switch (cell.getColumnIndex()) {
						case 0:
							id = (int) cell.getNumericCellValue();
							break;
						case 1:
							kodeKoptelProducts = cell.getStringCellValue();
							break;
						case 2:
							namaKoptelProduct = cell.getStringCellValue();
							break;
						}
					} catch (Exception e) {
					}

				}

				ListKoptelProduct listKoptelProduct = new ListKoptelProduct(id,
						kodeKoptelProducts, namaKoptelProduct, "123456");
				listListKoptelProduct.add(listKoptelProduct);

			}

			List<ListKoptelProduct> oldListKoptelProduct = listKoptelProductDao
					.getAllListKoptelProduct();
			for (int i = 0; i < oldListKoptelProduct.size(); i++) {
				listKoptelProductDao.delete(oldListKoptelProduct.get(i));
			}

			for (int i = 0; i < listListKoptelProduct.size(); i++) {
				listKoptelProductDao.save(listListKoptelProduct.get(i));
			}

			String type = "uploadListKoptelProduct";
			String status = "success";
			Date date = new Date();

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			re = "SUCCESS UPLOAD DATA LIST KOPTEL PRODUCT";

			fis.close();

		} catch (Exception e) {

			for (int i = 0; i < listListKoptelProduct.size(); i++) {
				listKoptelProductDao.delete(listListKoptelProduct.get(i));
			}

			String type = "uploadListKoptelProduct";
			String status = "failed";
			Date date = new Date();

			// FAILED IF FORMAT DATA LIST KOPTEL PRODUCT IS NOT MATCH WITH
			// DEFAULT FORMAT
			File filed = new File(path);
			filed.delete();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to upload. FORMAT DATA LIST KOPTEL PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

			re = "Failed to upload. FORMAT DATA LIST KOPTEL PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.";
		}

		return re;
	}

	// KOPEG
	public List<Kopeg> readKopeg(MultipartFile file, String path,
			String kodeKopeg, String months, String years, String user) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();
		boolean deleteFile = false;
		String kodeKopegs = "";

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			String namaKopeg = sheet.getRow(3).getCell(2).getStringCellValue();
			kodeKopegs = listKopegDao.getKodeKopeg(namaKopeg);

			if (kodeKopegs.equals(kodeKopeg)) {

				Iterator<Row> rowIterator = sheet.iterator();
				ROWITERATOR: while (rowIterator.hasNext()) {

					Row row = rowIterator.next();

					if (row.getRowNum() >= 15) {

						String nik = "";
						String nama = "";
						double simpanan = 0.0;
						double angsuran = 0.0;
						double lainnya = 0.0;
						double total = 0.0;
						Date awal = null;
						Date akhir = null;
						String keterangan = "";

						Iterator<Cell> cellIterator = row.cellIterator();

						while (cellIterator.hasNext()) {

							Cell cell = cellIterator.next();

							String temp = "";
							switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								temp = cell.getStringCellValue();
							}

							if (temp.equals("GRAND TOTAL")) {
								break ROWITERATOR;
							} else {
								if (cell.getColumnIndex() >= 1) {
									try {
										switch (cell.getColumnIndex()) {
										case 1:
											int niks = (int) cell
													.getNumericCellValue();
											nik = "" + niks;
											break;
										case 2:
											nama = cell.getStringCellValue();
											break;
										case 3:
											simpanan = cell
													.getNumericCellValue();
											break;
										case 4:
											angsuran = cell
													.getNumericCellValue();
											break;
										case 5:
											lainnya = cell
													.getNumericCellValue();
											break;
										case 6:
											total = cell.getNumericCellValue();
											break;
										case 7:
											awal = cell.getDateCellValue();
											break;
										case 8:
											akhir = cell.getDateCellValue();
											break;
										case 9:
											keterangan = cell
													.getStringCellValue();
											break;
										}
									} catch (Exception e) {
									}
								}
							}
						}// CELL ITERATOR WHILE END

						String validate = employeeDao.checkEmployeeToDB(nik,
								nama);

						if (validate.equals("false")) {
							deleteFile = true;
						}

						// KODE KOPEG DI EXCEL SESUAI DENGAN KODE KOPEG YG
						// DIKIRIM (KETENTUAN)
						Kopeg kopeg = new Kopeg(nik, nama, simpanan, angsuran,
								lainnya, total, awal, akhir, keterangan,
								validate, kodeKopeg, namaKopeg);
						listKopeg.add(kopeg);

					}// IF ROW NUMBER >=15 END

				}// ROW ITERATOR WHILE END

				String type = "uploadKopeg";
				String status = "success";
				Date date = new Date();

				// FAILED IF DATA EMPLOYEE IS INCORRECT
				if (deleteFile == true) {
					File filed = new File(path);
					filed.delete();
					status = "failed";
					path = "-";
				}

				FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
						years, status, path, user, date);
				fileStatusDao.saveOrUpdate(fileStatus);
			} else {

				String type = "uploadKopeg";
				String status = "failed";
				Date date = new Date();

				// FAILED IF DATA KOPEG IS NOT MATCH WITH LOGIN KOPEG
				File filed = new File(path);
				filed.delete();
				path = "-";

				FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
						years, status, path, user, date);
				fileStatusDao.saveOrUpdate(fileStatus);
			}

			fis.close();

		} catch (Exception e) {

			String type = "uploadKopeg";
			String status = "failed";
			Date date = new Date();

			// FAILED IF FORMAT DATA KOPEG IS NOT MATCH WITH DEFAULT FORMAT
			File filed = new File(path);
			filed.delete();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			listKopeg = null;
			System.out
					.println("Failed to upload. FORMAT DATA KOPEG IS NOT MATCH WITH DEFAULT FORMAT.");
		}

		return listKopeg;
	}

	public List<Employee> warningPensiunKopeg(List<Kopeg> listKopeg) {

		List<Employee> listEmp = new ArrayList<Employee>();

		Date date = new Date();

		Calendar cal = Calendar.getInstance();
		int daysToDecrement = -60; // 2 month

		for (int i = 0; i < listKopeg.size(); i++) {

			Employee emp = employeeDao.findById(listKopeg.get(i).getNik());

			Date pensiun = emp.getTglPensiunNorm();
			cal.setTime(pensiun);
			cal.add(Calendar.DATE, daysToDecrement);
			pensiun = cal.getTime();

			if (pensiun.before(date)) {
				listEmp.add(emp);
			}
		}

		return listEmp;
	}

	public List<FileStatus> findByDate(String month, String year) {

		return fileStatusDao.findByDate(month, year);
	}

	public String merge(String years, String months, String path,
			String kodeKopeg, String user) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();
		String dir = "";

		try {

			File directory = new File(path);
			File[] fList = directory.listFiles();

			if (fList.length == 0) {
				dir = "null";
			}

			for (File file : fList) {

				String fileName = path + "" + file.getName();
				FileInputStream fis = new FileInputStream(fileName);

				Workbook workbook = null;

				if (fileName.toLowerCase().endsWith("xlsx")) {
					workbook = new XSSFWorkbook(fis);
				} else if (path.toLowerCase().endsWith("xls")) {
					workbook = new HSSFWorkbook(fis);
				}

				Sheet sheet = workbook.getSheet("Sheet1");

				String namaKopeg = sheet.getRow(3).getCell(2)
						.getStringCellValue();

				Iterator<Row> rowIterator = sheet.iterator();
				ROWITERATOR: while (rowIterator.hasNext()) {

					Row row = rowIterator.next();

					if (row.getRowNum() >= 15) {

						String nik = "";
						String nama = "";
						double simpanan = 0.0;
						double angsuran = 0.0;
						double lainnya = 0.0;
						double total = 0.0;
						Date awal = null;
						Date akhir = null;
						String keterangan = "";

						Iterator<Cell> cellIterator = row.cellIterator();

						while (cellIterator.hasNext()) {
							Cell cell = cellIterator.next();

							String temp = "";
							switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								temp = cell.getStringCellValue();
							}

							if (temp.equals("GRAND TOTAL")) {
								break ROWITERATOR;
							} else {
								if (cell.getColumnIndex() >= 1) {
									try {
										switch (cell.getColumnIndex()) {
										case 1:
											int niks = (int) cell
													.getNumericCellValue();
											nik = "" + niks;
											break;
										case 2:
											nama = cell.getStringCellValue();
											break;
										case 3:
											simpanan = cell
													.getNumericCellValue();
											break;
										case 4:
											angsuran = cell
													.getNumericCellValue();
											break;
										case 5:
											lainnya = cell
													.getNumericCellValue();
											break;
										case 6:
											total = cell.getNumericCellValue();
											break;
										case 7:
											awal = cell.getDateCellValue();
											break;
										case 8:
											akhir = cell.getDateCellValue();
											break;
										case 9:
											keterangan = cell
													.getStringCellValue();
											break;
										}
									} catch (Exception e) {
									}
								}
							}
						}// CELL ITERATOR WHILE END

						String kodeKopegs = listKopegDao
								.getKodeKopeg(namaKopeg);

						// KODE KOPEGS SESUAI DENGAN EXCEL, KARENA KODE KOPEG YG
						// DIKIRIM ADALAH KODE KOPEG KOPTEL
						Kopeg kopeg = new Kopeg(nik, nama, simpanan, angsuran,
								lainnya, total, awal, akhir, keterangan,
								"true", kodeKopegs, namaKopeg);
						listKopeg.add(kopeg);

					}// IF ROW NUMBER >=15 END
				}// ROW ITERATOR WHILE END

				fis.close();
			}

			if (!dir.equals("null")) {

				List<Kopeg> listKopegFinal = new ArrayList<Kopeg>();

				// MERGE WHICH SAME NIK
				for (int i = 0; i < listKopeg.size(); i++) {

					String nik = listKopeg.get(i).getNik();
					String nama = listKopeg.get(i).getNama();
					double simpanan = listKopeg.get(i).getSimpanan();
					double angsuran = listKopeg.get(i).getAngsuran();
					double lainnya = listKopeg.get(i).getLainnya();
					double total = listKopeg.get(i).getTotal();
					Date awal = listKopeg.get(i).getAwal();
					Date akhir = listKopeg.get(i).getAkhir();
					String keterangan = listKopeg.get(i).getKeterangan();
					String validate = listKopeg.get(i).getValidate();
					String kodeKopegs = "merge";
					String namaKopeg = "merge";

					boolean statusFinal = false;
					int indexFinal = 0;

					for (int j = 0; j < listKopegFinal.size(); j++) {

						if (nik.equals(listKopegFinal.get(j).getNik())) {

							statusFinal = true;
							indexFinal = j;
						}
					}

					if (statusFinal == true) {

						simpanan = simpanan
								+ listKopeg.get(indexFinal).getSimpanan();
						angsuran = angsuran
								+ listKopeg.get(indexFinal).getAngsuran();
						lainnya = lainnya
								+ listKopeg.get(indexFinal).getLainnya();
						total = total + listKopeg.get(indexFinal).getTotal();

						// UNTUK KOPEG

						// BEGDA YG BULANNYA LEBIH BESAR YG DIAMBIL
						if (listKopeg.get(indexFinal).getAwal().compareTo(awal) > 0) {
							awal = listKopeg.get(indexFinal).getAwal();
						}

						// ENDA YG BULANNYA LEBIH BESAR YG DIAMBIL
						if (listKopeg.get(indexFinal).getAkhir()
								.compareTo(akhir) > 0) {
							akhir = listKopeg.get(indexFinal).getAkhir();
						}

						// // UNTUK KOPEG PRODUK
						//
						// // BEGDA YG BULANNYA LEBIH BESAR YG DIAMBIL
						// if(listKopeg.get(indexFinal).getAwal().compareTo(awal)
						// > 0) {
						// awal = listKopeg.get(indexFinal).getAwal();
						// }
						//
						// // ENDA YG BULANNYA LEBIH KECIL YG DIAMBIL
						// if(listKopeg.get(indexFinal).getAkhir().compareTo(akhir)
						// < 0) {
						// akhir = listKopeg.get(indexFinal).getAkhir();
						// }

						Kopeg kopeg = new Kopeg(nik, nama, simpanan, angsuran,
								lainnya, total, awal, akhir, keterangan,
								validate, kodeKopegs, namaKopeg);

						listKopegFinal.set(indexFinal, kopeg);

					} else {

						Kopeg kopeg = new Kopeg(nik, nama, simpanan, angsuran,
								lainnya, total, awal, akhir, keterangan,
								validate, kodeKopegs, namaKopeg);
						listKopegFinal.add(kopeg);
					}
				}

				Workbook workbook = new XSSFWorkbook();
				Sheet sheet = workbook.createSheet("Sheet1");

				// HEADER
				CellStyle style = workbook.createCellStyle();
				style.setFillForegroundColor(IndexedColors.YELLOW.index);
				style.setFillPattern(CellStyle.SOLID_FOREGROUND);
				style.setAlignment(CellStyle.ALIGN_CENTER);
				style.setBorderRight(CellStyle.BORDER_THIN);
				style.setBorderLeft(CellStyle.BORDER_THIN);
				style.setBorderTop(CellStyle.BORDER_THIN);
				style.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont font = (XSSFFont) workbook.createFont();
				font.setBold(true);
				style.setFont(font);

				// DATA
				CellStyle style2 = workbook.createCellStyle();
				style2.setBorderRight(CellStyle.BORDER_THIN);
				style2.setBorderLeft(CellStyle.BORDER_THIN);
				style2.setBorderTop(CellStyle.BORDER_THIN);
				style2.setBorderBottom(CellStyle.BORDER_THIN);

				Iterator<Kopeg> iterator = listKopegFinal.iterator();

				int rowIndex = 0;
				while (iterator.hasNext()) {

					Row row = sheet.createRow(rowIndex++);

					if (rowIndex == 1) {

						Cell cell0 = row.createCell(0);
						cell0.setCellValue("NIK");
						cell0.setCellStyle(style);

						Cell cell1 = row.createCell(1);
						cell1.setCellValue("NAMA");
						cell1.setCellStyle(style);

						Cell cell2 = row.createCell(2);
						cell2.setCellValue("SIMPANAN");
						cell2.setCellStyle(style);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue("ANGSURAN");
						cell3.setCellStyle(style);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue("LAINNYA");
						cell4.setCellStyle(style);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue("TOTAL");
						cell5.setCellStyle(style);

						Cell cell6 = row.createCell(6);
						cell6.setCellValue("AWAL");
						cell6.setCellStyle(style);

						Cell cell7 = row.createCell(7);
						cell7.setCellValue("AKHIR");
						cell7.setCellStyle(style);

						Cell cell8 = row.createCell(8);
						cell8.setCellValue("KETERANGAN");
						cell8.setCellStyle(style);

						Cell cell9 = row.createCell(9);
						cell9.setCellValue("VALIDATE");
						cell9.setCellStyle(style);

						Cell cell10 = row.createCell(10);
						cell10.setCellValue("KODE_KOPEG");
						cell10.setCellStyle(style);

						Cell cell11 = row.createCell(11);
						cell11.setCellValue("NAMA_KOPEG");
						cell11.setCellStyle(style);
					} else {

						Kopeg kopeg = iterator.next();

						Cell cell0 = row.createCell(0);
						cell0.setCellStyle(style2);
						if (kopeg.getNik() != null) {
							cell0.setCellValue(kopeg.getNik());
						}

						Cell cell1 = row.createCell(1);
						cell1.setCellStyle(style2);
						if (kopeg.getNama() != null) {
							cell1.setCellValue(kopeg.getNama());
						}

						Cell cell2 = row.createCell(2);
						cell2.setCellValue(kopeg.getSimpanan());
						cell2.setCellStyle(style2);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue(kopeg.getAngsuran());
						cell3.setCellStyle(style2);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue(kopeg.getLainnya());
						cell4.setCellStyle(style2);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue(kopeg.getTotal());
						cell5.setCellStyle(style2);

						Cell cell6 = row.createCell(6);
						cell6.setCellStyle(style2);
						if (kopeg.getAwal() != null) {
							cell6.setCellValue(kopeg.getAwal());
						}

						Cell cell7 = row.createCell(7);
						cell7.setCellStyle(style2);
						if (kopeg.getAkhir() != null) {
							cell7.setCellValue(kopeg.getAkhir());
						}

						Cell cell8 = row.createCell(8);
						cell8.setCellStyle(style2);
						if (kopeg.getKeterangan() != null) {
							cell8.setCellValue(kopeg.getKeterangan());
						}

						Cell cell9 = row.createCell(9);
						cell9.setCellStyle(style2);
						if (kopeg.getValidate() != null) {
							cell9.setCellValue(kopeg.getValidate());
						}

						Cell cell10 = row.createCell(10);
						cell10.setCellStyle(style2);
						if (kopeg.getKodeKopeg() != null) {
							cell10.setCellValue(kopeg.getKodeKopeg());
						}

						Cell cell11 = row.createCell(11);
						cell11.setCellStyle(style2);
						if (kopeg.getNamaKopeg() != null) {
							cell11.setCellValue(kopeg.getNamaKopeg());
						}
					}
				}

				for (int a = 0; a <= 11; a++) {
					sheet.autoSizeColumn(a);
				}

				// SUMMARIZE DETAIL
				Workbook workbook2 = new XSSFWorkbook();
				Sheet sheet2 = workbook2.createSheet("Sheet1");

				// HEADER
				CellStyle styles = workbook2.createCellStyle();
				styles.setFillForegroundColor(IndexedColors.YELLOW.index);
				styles.setFillPattern(CellStyle.SOLID_FOREGROUND);
				styles.setAlignment(CellStyle.ALIGN_CENTER);
				styles.setBorderRight(CellStyle.BORDER_THIN);
				styles.setBorderLeft(CellStyle.BORDER_THIN);
				styles.setBorderTop(CellStyle.BORDER_THIN);
				styles.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont fonts = (XSSFFont) workbook2.createFont();
				fonts.setBold(true);
				styles.setFont(fonts);

				// DATA
				CellStyle styles2 = workbook2.createCellStyle();
				styles2.setBorderRight(CellStyle.BORDER_THIN);
				styles2.setBorderLeft(CellStyle.BORDER_THIN);
				styles2.setBorderTop(CellStyle.BORDER_THIN);
				styles2.setBorderBottom(CellStyle.BORDER_THIN);

				Iterator<Kopeg> iterator2 = listKopeg.iterator();

				int rowIndex2 = 0;
				while (iterator2.hasNext()) {

					Row row = sheet2.createRow(rowIndex2++);

					if (rowIndex2 == 1) {

						Cell cell0 = row.createCell(0);
						cell0.setCellValue("NIK");
						cell0.setCellStyle(styles);

						Cell cell1 = row.createCell(1);
						cell1.setCellValue("NAMA");
						cell1.setCellStyle(styles);

						Cell cell2 = row.createCell(2);
						cell2.setCellValue("SIMPANAN");
						cell2.setCellStyle(styles);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue("ANGSURAN");
						cell3.setCellStyle(styles);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue("LAINNYA");
						cell4.setCellStyle(styles);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue("TOTAL");
						cell5.setCellStyle(styles);

						Cell cell6 = row.createCell(6);
						cell6.setCellValue("AWAL");
						cell6.setCellStyle(styles);

						Cell cell7 = row.createCell(7);
						cell7.setCellValue("AKHIR");
						cell7.setCellStyle(styles);

						Cell cell8 = row.createCell(8);
						cell8.setCellValue("KETERANGAN");
						cell8.setCellStyle(styles);

						Cell cell9 = row.createCell(9);
						cell9.setCellValue("VALIDATE");
						cell9.setCellStyle(styles);

						Cell cell10 = row.createCell(10);
						cell10.setCellValue("KODE_KOPEG");
						cell10.setCellStyle(styles);

						Cell cell11 = row.createCell(11);
						cell11.setCellValue("NAMA_KOPEG");
						cell11.setCellStyle(styles);
					} else {

						Kopeg kopeg = iterator2.next();

						Cell cell0 = row.createCell(0);
						cell0.setCellStyle(styles2);
						if (kopeg.getNik() != null) {
							cell0.setCellValue(kopeg.getNik());
						}

						Cell cell1 = row.createCell(1);
						cell1.setCellStyle(styles2);
						if (kopeg.getNama() != null) {
							cell1.setCellValue(kopeg.getNama());
						}

						Cell cell2 = row.createCell(2);
						cell2.setCellValue(kopeg.getSimpanan());
						cell2.setCellStyle(styles2);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue(kopeg.getAngsuran());
						cell3.setCellStyle(styles2);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue(kopeg.getLainnya());
						cell4.setCellStyle(styles2);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue(kopeg.getTotal());
						cell5.setCellStyle(styles2);

						Cell cell6 = row.createCell(6);
						cell6.setCellStyle(styles2);
						if (kopeg.getAwal() != null) {
							cell6.setCellValue(kopeg.getAwal());
						}

						Cell cell7 = row.createCell(7);
						cell7.setCellStyle(styles2);
						if (kopeg.getAkhir() != null) {
							cell7.setCellValue(kopeg.getAkhir());
						}

						Cell cell8 = row.createCell(8);
						cell8.setCellStyle(styles2);
						if (kopeg.getKeterangan() != null) {
							cell8.setCellValue(kopeg.getKeterangan());
						}

						Cell cell9 = row.createCell(9);
						cell9.setCellStyle(styles2);
						if (kopeg.getValidate() != null) {
							cell9.setCellValue(kopeg.getValidate());
						}

						Cell cell10 = row.createCell(10);
						cell10.setCellStyle(styles2);
						if (kopeg.getKodeKopeg() != null) {
							cell10.setCellValue(kopeg.getKodeKopeg());
						}

						Cell cell11 = row.createCell(11);
						cell11.setCellStyle(styles2);
						if (kopeg.getNamaKopeg() != null) {
							cell11.setCellValue(kopeg.getNamaKopeg());
						}
					}
				}

				for (int a = 0; a <= 11; a++) {
					sheet2.autoSizeColumn(a);
				}

				String directories = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\"
						+ years + "\\" + months;

				File files = new File(directories);
				if (!files.exists()) {
					if (files.mkdirs()) {
					}
				}

				String fileName1 = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/"
						+ years + "/" + months + "/Summarize.xlsx";

				String fileName2 = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/"
						+ years + "/" + months + "/SummarizeDetail.xlsx";

				FileOutputStream fos1;
				FileOutputStream fos2;
				try {
					fos1 = new FileOutputStream(fileName1);
					fos2 = new FileOutputStream(fileName2);
					workbook.write(fos1);
					workbook2.write(fos2);
					fos1.close();
					fos2.close();
				} catch (Exception e) {

					String type = "merge";
					String status = "failed";
					Date date = new Date();
					path = "-";

					FileStatus fileStatus = new FileStatus(kodeKopeg, type,
							months, years, status, path, user, date);
					fileStatusDao.saveOrUpdate(fileStatus);

					System.out
							.println("Failed to summarize. CANNOT SUMMARIZE DATA KOPEG BECAUSE SYSTEM CANNOT WRITE SUMMARIZE TO DIRECTORY.");

					return "Failed to summarize. CANNOT SUMMARIZE DATA KOPEG BECAUSE SYSTEM CANNOT WRITE SUMMARIZE TO DIRECTORY.";
				}

				String status = mergeSplit(years, months, fileName1);

				if (status.equals("SUCCESS")) {
					String type = "merge";
					String statuss = "success";
					Date date = new Date();

					String zip = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/"
							+ years + "/" + months + "-Merge.zip";

					FileStatus fileStatus = new FileStatus(kodeKopeg, type,
							months, years, statuss, zip, user, date);
					fileStatusDao.saveOrUpdate(fileStatus);

					return "SUCCESS SUMMARIZE DATA KOPEG";
				} else {

					String type = "merge";
					String statuss = "failed";
					Date date = new Date();
					path = "-";

					FileStatus fileStatus = new FileStatus(kodeKopeg, type,
							months, years, statuss, path, user, date);
					fileStatusDao.saveOrUpdate(fileStatus);

					return "Failed to summarize. SPLIT SUMMARIZE FAILED.";
				}
			} else {

				String type = "merge";
				String status = "failed";
				Date date = new Date();
				path = "-";

				FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
						years, status, path, user, date);
				fileStatusDao.saveOrUpdate(fileStatus);

				return "Failed to summarize. NO FILE KOPEG SUCCESS UPLOAD.";
			}

		} catch (Exception e) {

			String type = "merge";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to summarize. CANNOT ACCESS THE FILE BECAUSE IT IS BEING USED BY ANOTHER PROCESS.");

			return "Failed to summarize. CANNOT ACCESS THE FILE BECAUSE IT IS BEING USED BY ANOTHER PROCESS.";
		}
	}

	public String mergeSplit(String years, String months, String path) {

		String status = delimitAngsuran(years, months, path);
		status = delimitSimpanan(years, months, path);
		status = perubahanAngsuran(years, months, path);
		status = perubahanSimpanan(years, months, path);
		status = angsuranLainLain(years, months, path);

		String zip = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years + "\\"
				+ months + "-Merge.zip";
		String source = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years
				+ "\\" + months;
		status = zipFile(years, months, zip, source);
		return status;
	}

	public String zipFile(String years, String months, String zip, String source) {

		fileList = new ArrayList<String>();
		String OUTPUT_ZIP_FILE = zip;
		String SOURCE_FOLDER = source;

		generateFileList(new File(SOURCE_FOLDER), SOURCE_FOLDER);
		zipIt(OUTPUT_ZIP_FILE, SOURCE_FOLDER);

		return "SUCCESS";
	}

	public void generateFileList(File node, String SOURCE_FOLDER) {

		if (node.isFile()) {
			fileList.add(generateZipEntry(node.getAbsoluteFile().toString(),
					SOURCE_FOLDER));
		}

		if (node.isDirectory()) {
			String[] subNote = node.list();
			for (String filename : subNote) {
				generateFileList(new File(node, filename), SOURCE_FOLDER);
			}
		}

	}

	public void zipIt(String zipFile, String SOURCE_FOLDER) {

		byte[] buffer = new byte[1024];

		try {

			FileOutputStream fos = new FileOutputStream(zipFile);
			ZipOutputStream zos = new ZipOutputStream(fos);

			for (String file : this.fileList) {

				ZipEntry ze = new ZipEntry(file);
				zos.putNextEntry(ze);

				FileInputStream in = new FileInputStream(SOURCE_FOLDER
						+ File.separator + file);

				int len;
				while ((len = in.read(buffer)) > 0) {
					zos.write(buffer, 0, len);
				}

				in.close();
			}

			zos.closeEntry();
			zos.close();

		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}

	public String generateZipEntry(String file, String SOURCE_FOLDER) {
		return file.substring(SOURCE_FOLDER.length() + 1, file.length());
	}

	public String delimitAngsuran(String years, String months, String path) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String subtype = "";
					Date akhir = null;
					String nama = "";
					double angsuran = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 3:
								angsuran = cell.getNumericCellValue();
								break;
							case 7:
								akhir = cell.getDateCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}

					if (angsuran == 0.0) {
						Kopeg kopeg = new Kopeg(nik, subtype, akhir, nama);
						listKopeg.add(kopeg);
					}

				}
			}

			fis.close();

		} catch (IOException e) {
			e.printStackTrace();
		}

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<Kopeg> iterator = listKopeg.iterator();

		int rowIndex = 0;
		while (iterator.hasNext()) {
			Kopeg kopeg = iterator.next();
			Row row = sheet.createRow(rowIndex++);

			if (rowIndex == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("SUBTYPE");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("AKHIR");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("NAMA");
				cell3.setCellStyle(style);

			} else {

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(style2);
				if (kopeg.getNik() != null) {
					cell0.setCellValue(kopeg.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(style2);
				if (kopeg.getSubtype() != null) {
					cell1.setCellValue(kopeg.getSubtype());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellStyle(style2);
				if (kopeg.getAkhir() != null) {
					cell2.setCellValue(kopeg.getAkhir());
				}

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(style2);
				if (kopeg.getNama() != null) {
					cell3.setCellValue(kopeg.getNama());
				}

			}
		}

		for (int a = 0; a <= 3; a++) {
			sheet.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years
				+ "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
				+ "/" + months + "/" + "DelimitAngsuran.xlsx";

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return "SUCCESS";

	}

	public String delimitSimpanan(String years, String months, String path) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String subtype = "";
					Date awal = null;
					String nama = "";
					double simpanan = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 2:
								simpanan = cell.getNumericCellValue();
								break;
							case 6:
								awal = cell.getDateCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}

					if (simpanan == 0.0) {
						Kopeg kopeg = new Kopeg(nik, subtype, awal, nama,
								simpanan);
						listKopeg.add(kopeg);
					}

				}
			}

			fis.close();

		} catch (IOException e) {
			e.printStackTrace();
		}

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<Kopeg> iterator = listKopeg.iterator();

		int rowIndex = 0;
		while (iterator.hasNext()) {
			Kopeg kopeg = iterator.next();
			Row row = sheet.createRow(rowIndex++);

			if (rowIndex == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("SUBTYPE");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("AWAL");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("NAMA");
				cell3.setCellStyle(style);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("SIMPANAN");
				cell4.setCellStyle(style);

			} else {

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(style2);
				if (kopeg.getNik() != null) {
					cell0.setCellValue(kopeg.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(style2);
				if (kopeg.getSubtype() != null) {
					cell1.setCellValue(kopeg.getSubtype());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellStyle(style2);
				if (kopeg.getAwal() != null) {
					cell2.setCellValue(kopeg.getAwal());
				}

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(style2);
				if (kopeg.getNama() != null) {
					cell3.setCellValue(kopeg.getNama());
				}

				Cell cell4 = row.createCell(4);
				cell4.setCellValue(kopeg.getSimpanan());
				cell4.setCellStyle(style2);

			}
		}

		for (int a = 0; a <= 4; a++) {
			sheet.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years
				+ "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
				+ "/" + months + "/" + "DelimitSimpanan.xlsx";

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return "SUCCESS";

	}

	public String perubahanAngsuran(String years, String months, String path) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String subtype = "";
					Date awal = null;
					Date akhir = null;
					String kodeKopeg = "";
					String namaKopeg = "";
					double angsuran = 0.0;
					String nama = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 3:
								angsuran = cell.getNumericCellValue();
								break;
							case 6:
								awal = cell.getDateCellValue();
								break;
							case 7:
								akhir = cell.getDateCellValue();
								break;
							case 10:
								kodeKopeg = cell.getStringCellValue();
								break;
							case 11:
								namaKopeg = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}
					}

					if (angsuran != 0.0) {
						Kopeg kopeg = new Kopeg(nik, subtype, awal, akhir,
								kodeKopeg, namaKopeg, angsuran, nama);
						listKopeg.add(kopeg);
					}
				}
			}

			fis.close();

		} catch (IOException e) {
			e.printStackTrace();
		}

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<Kopeg> iterator = listKopeg.iterator();

		int rowIndex = 0;
		while (iterator.hasNext()) {
			Kopeg kopeg = iterator.next();
			Row row = sheet.createRow(rowIndex++);

			if (rowIndex == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("SUBTYPE");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("AWAL");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("AKHIR");
				cell3.setCellStyle(style);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("KODE_KOPEG");
				cell4.setCellStyle(style);

				Cell cell5 = row.createCell(5);
				cell5.setCellValue("NAMA_KOPEG");
				cell5.setCellStyle(style);

				Cell cell6 = row.createCell(6);
				cell6.setCellValue("ANGSURAN");
				cell6.setCellStyle(style);

				Cell cell7 = row.createCell(7);
				cell7.setCellValue("NAMA");
				cell7.setCellStyle(style);
			} else {

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(style2);
				if (kopeg.getNik() != null) {
					cell0.setCellValue(kopeg.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(style2);
				if (kopeg.getSubtype() != null) {
					cell1.setCellValue(kopeg.getSubtype());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellStyle(style2);
				if (kopeg.getAwal() != null) {
					cell2.setCellValue(kopeg.getAwal());
				}

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(style2);
				if (kopeg.getAkhir() != null) {
					cell3.setCellValue(kopeg.getAkhir());
				}

				Cell cell4 = row.createCell(4);
				cell4.setCellStyle(style2);
				if (kopeg.getKodeKopeg() != null) {
					cell4.setCellValue(kopeg.getKodeKopeg());
				}

				Cell cell5 = row.createCell(5);
				cell5.setCellStyle(style2);
				if (kopeg.getNamaKopeg() != null) {
					cell5.setCellValue(kopeg.getNamaKopeg());
				}

				Cell cell6 = row.createCell(6);
				cell6.setCellValue(kopeg.getAngsuran());
				cell6.setCellStyle(style2);

				Cell cell7 = row.createCell(7);
				cell7.setCellStyle(style2);
				if (kopeg.getNama() != null) {
					cell7.setCellValue(kopeg.getNama());
				}

			}
		}

		for (int a = 0; a <= 7; a++) {
			sheet.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years
				+ "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
				+ "/" + months + "/" + "PerubahanAngsuran.xlsx";

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return "SUCCESS";

	}

	public String perubahanSimpanan(String years, String months, String path) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");
			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String subtype = "";
					Date awal = null;
					Date akhir = null;
					String kodeKopeg = "";
					double simpanan = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 2:
								simpanan = cell.getNumericCellValue();
								break;
							case 6:
								awal = cell.getDateCellValue();
								break;
							case 7:
								akhir = cell.getDateCellValue();
								break;
							case 10:
								kodeKopeg = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}
					}

					if (simpanan != 0.0) {
						Kopeg kopeg = new Kopeg(nik, subtype, awal, akhir,
								kodeKopeg, simpanan);
						listKopeg.add(kopeg);
					}
				}
			}

			fis.close();

		} catch (IOException e) {
			e.printStackTrace();
		}

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<Kopeg> iterator = listKopeg.iterator();

		int rowIndex = 0;
		while (iterator.hasNext()) {
			Kopeg kopeg = iterator.next();
			Row row = sheet.createRow(rowIndex++);

			if (rowIndex == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("SUBTYPE");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("AWAL");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("AKHIR");
				cell3.setCellStyle(style);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("KODE_KOPEG");
				cell4.setCellStyle(style);

				Cell cell5 = row.createCell(5);
				cell5.setCellValue("SIMPANAN");
				cell5.setCellStyle(style);
			} else {

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(style2);
				if (kopeg.getNik() != null) {
					cell0.setCellValue(kopeg.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(style2);
				if (kopeg.getSubtype() != null) {
					cell1.setCellValue(kopeg.getSubtype());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellStyle(style2);
				if (kopeg.getAwal() != null) {
					cell2.setCellValue(kopeg.getAwal());
				}

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(style2);
				if (kopeg.getAkhir() != null) {
					cell3.setCellValue(kopeg.getAkhir());
				}

				Cell cell4 = row.createCell(4);
				cell4.setCellStyle(style2);
				if (kopeg.getKodeKopeg() != null) {
					cell4.setCellValue(kopeg.getKodeKopeg());
				}

				Cell cell5 = row.createCell(5);
				cell5.setCellValue(kopeg.getSimpanan());
				cell5.setCellStyle(style2);

			}
		}

		for (int a = 0; a <= 5; a++) {
			sheet.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years
				+ "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
				+ "/" + months + "/" + "PerubahanSimpanan.xlsx";

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return "SUCCESS";

	}

	public String angsuranLainLain(String years, String months, String path) {

		List<Kopeg> listKopeg = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String nama = "";
					String kodeKopeg = "";
					String namaKopeg = "";
					double lainnya = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 4:
								lainnya = cell.getNumericCellValue();
								break;
							case 10:
								kodeKopeg = cell.getStringCellValue();
								break;
							case 11:
								namaKopeg = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}
					}
					if (lainnya != 0.0) {
						Kopeg kopeg = new Kopeg(nik, nama, kodeKopeg,
								namaKopeg, lainnya);
						listKopeg.add(kopeg);
					}
				}
			}

			fis.close();

		} catch (IOException e) {
			e.printStackTrace();
		}

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<Kopeg> iterator = listKopeg.iterator();

		int rowIndex = 0;
		while (iterator.hasNext()) {
			Kopeg kopeg = iterator.next();
			Row row = sheet.createRow(rowIndex++);

			if (rowIndex == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("NAMA");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("KODE_KOPEG");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("NAMA_KOPEG");
				cell3.setCellStyle(style);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("LAINNYA");
				cell4.setCellStyle(style);
			} else {

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(style2);
				if (kopeg.getNik() != null) {
					cell0.setCellValue(kopeg.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(style2);
				if (kopeg.getNama() != null) {
					cell1.setCellValue(kopeg.getNama());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellStyle(style2);
				if (kopeg.getKodeKopeg() != null) {
					cell2.setCellValue(kopeg.getKodeKopeg());
				}

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(style2);
				if (kopeg.getNamaKopeg() != null) {
					cell3.setCellValue(kopeg.getNamaKopeg());
				}

				Cell cell4 = row.createCell(4);
				cell4.setCellValue(kopeg.getLainnya());
				cell4.setCellStyle(style2);

			}
		}

		for (int a = 0; a <= 4; a++) {
			sheet.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Merge\\" + years
				+ "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
				+ "/" + months + "/" + "AngsuranLainLain.xlsx";

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

		return "SUCCESS";

	}

	public String validationCompareSimpananTetap(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					String akhirs = null;
					double simpanan = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 4:
									awals = cell.getStringCellValue();
									break;
								case 5:
									akhirs = cell.getStringCellValue();
									break;
								case 8:
									simpanan = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
								System.out
										.println("Failed to upload. FORMAT DATA COMPARE SIMPANAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

								return "false";
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);
					Date akhir = dateParsing(akhirs);

					Kopeg kopeg = new Kopeg(nik, awal, akhir, simpanan);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA COMPARE SIMPANAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public String validationCompareAngsuranTetap(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					String akhirs = null;
					double angsuran = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 4:
									awals = cell.getStringCellValue();
									break;
								case 5:
									akhirs = cell.getStringCellValue();
									break;
								case 8:
									angsuran = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
								System.out
										.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

								return "false";
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);
					Date akhir = dateParsing(akhirs);

					Kopeg kopeg = new Kopeg(nik, awal, akhir, angsuran);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public String validationCompareAngsuranLainLain(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					double lainnya = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 3:
									awals = cell.getStringCellValue();
									break;
								case 5:
									lainnya = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
								System.out
										.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN LAIN LAIN IS NOT MATCH WITH DEFAULT FORMAT.");

								return "false";
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);

					Kopeg kopeg = new Kopeg(nik, awal, lainnya);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN LAIN LAIN IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public List<FileStatus> findCompare(String month, String year) {

		return fileStatusDao.findCompare(month, year);
	}

	public String getDifference(String years, String months, String path,
			String kodeKopeg, String user) {

		listDifference = new ArrayList<Kopeg>();
		listAllCompare = new ArrayList<Kopeg>();

		List<Kopeg> listMerge = new ArrayList<Kopeg>();

		try {

			String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
					+ "/" + months + "/" + "Summarize.xlsx";
			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String nama = "";
					double simpanan = 0.0;
					double angsuran = 0.0;
					double lainnya = 0.0;
					double total = 0.0;
					Date awal = null;
					Date akhir = null;
					String keterangan = "";
					String validate = "";
					String kodeKopegs = "";
					String namaKopeg = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 2:
								simpanan = cell.getNumericCellValue();
								break;
							case 3:
								angsuran = cell.getNumericCellValue();
								break;
							case 4:
								lainnya = cell.getNumericCellValue();
								break;
							case 5:
								total = cell.getNumericCellValue();
								break;
							case 6:
								awal = cell.getDateCellValue();
								break;
							case 7:
								akhir = cell.getDateCellValue();
								break;
							case 8:
								keterangan = cell.getStringCellValue();
								break;
							case 9:
								validate = cell.getStringCellValue();
								break;
							case 10:
								kodeKopegs = cell.getStringCellValue();
								break;
							case 11:
								namaKopeg = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					Kopeg kopeg = new Kopeg(nik, nama, simpanan, angsuran,
							lainnya, total, awal, akhir, keterangan, validate,
							kodeKopegs, namaKopeg);
					listMerge.add(kopeg);

				}// IF ROW NUMBER >=1 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			String type = "compare";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to compare. FILE SUMMARIZE IS NOT FOUND IN DIRECTORY.");

			return "Failed to compare. FILE SUMMARIZE IS NOT FOUND IN DIRECTORY.";
		}

		File directories = new File(path);
		File[] fList = directories.listFiles();
		String dir = "";

		if (fList.length == 0) {
			dir = "null";
		}

		if (dir.equals("null")) {

			String type = "compare";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			return "Failed to compare. FILE COMPARE DATA IS NOT FOUND IN DIRECTORY.";
		}

		for (File file : fList) {

			String fileName = path + "" + file.getName();

			if (file.getName().startsWith("CompareAngsuranTetap")) {
				checkCompareAngsuranTetap(listMerge, years, months, fileName,
						kodeKopeg, user);
			} else {
				if (file.getName().startsWith("CompareSimpananTetap")) {
					checkCompareSimpananTetap(listMerge, years, months,
							fileName, kodeKopeg, user);
				} else {
					if (file.getName().startsWith("CompareAngsuranLainLain")) {
						checkCompareAngsuranLainLain(listMerge, years, months,
								fileName, kodeKopeg, user);
					}
				}
			}

		}

		checkDifferenceAllCompare(listMerge);

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Row row = sheet.createRow(0);

		Cell cell0 = row.createCell(0);
		cell0.setCellValue("NIK");
		cell0.setCellStyle(style);

		Cell cell1 = row.createCell(1);
		cell1.setCellValue("NAMA");
		cell1.setCellStyle(style);

		Cell cell2 = row.createCell(2);
		cell2.setCellValue("SIMPANAN");
		cell2.setCellStyle(style);

		Cell cell3 = row.createCell(3);
		cell3.setCellValue("ANGSURAN");
		cell3.setCellStyle(style);

		Cell cell4 = row.createCell(4);
		cell4.setCellValue("LAINNYA");
		cell4.setCellStyle(style);

		Cell cell5 = row.createCell(5);
		cell5.setCellValue("TOTAL");
		cell5.setCellStyle(style);

		Cell cell6 = row.createCell(6);
		cell6.setCellValue("AWAL");
		cell6.setCellStyle(style);

		Cell cell7 = row.createCell(7);
		cell7.setCellValue("AKHIR");
		cell7.setCellStyle(style);

		Cell cell8 = row.createCell(8);
		cell8.setCellValue("KETERANGAN");
		cell8.setCellStyle(style);

		Cell cell9 = row.createCell(9);
		cell9.setCellValue("VALIDATE");
		cell9.setCellStyle(style);

		Cell cell10 = row.createCell(10);
		cell10.setCellValue("KODE_KOPEG");
		cell10.setCellStyle(style);

		Cell cell11 = row.createCell(11);
		cell11.setCellValue("NAMA_KOPEG");
		cell11.setCellStyle(style);

		Iterator<Kopeg> iterator = listDifference.iterator();

		int rowIndex = 1;
		while (iterator.hasNext()) {

			Row rows = sheet.createRow(rowIndex++);
			Kopeg kopeg = iterator.next();

			Cell cells0 = rows.createCell(0);
			cells0.setCellStyle(style2);
			if (kopeg.getNik() != null) {
				cells0.setCellValue(kopeg.getNik());
			}

			Cell cells1 = rows.createCell(1);
			cells1.setCellStyle(style2);
			if (kopeg.getNama() != null) {
				cells1.setCellValue(kopeg.getNama());
			}

			Cell cells2 = rows.createCell(2);
			cells2.setCellValue(kopeg.getSimpanan());
			cells2.setCellStyle(style2);

			Cell cells3 = rows.createCell(3);
			cells3.setCellValue(kopeg.getAngsuran());
			cells3.setCellStyle(style2);

			Cell cells4 = rows.createCell(4);
			cells4.setCellValue(kopeg.getLainnya());
			cells4.setCellStyle(style2);

			Cell cells5 = rows.createCell(5);
			cells5.setCellValue(kopeg.getTotal());
			cells5.setCellStyle(style2);

			Cell cells6 = rows.createCell(6);
			cells6.setCellStyle(style2);
			if (kopeg.getAwal() != null) {
				cells6.setCellValue(kopeg.getAwal());
			}

			Cell cells7 = rows.createCell(7);
			cells7.setCellStyle(style2);
			if (kopeg.getAkhir() != null) {
				cells7.setCellValue(kopeg.getAkhir());
			}

			Cell cells8 = rows.createCell(8);
			cells8.setCellStyle(style2);
			if (kopeg.getKeterangan() != null) {
				cells8.setCellValue(kopeg.getKeterangan());
			}

			Cell cells9 = rows.createCell(9);
			cells9.setCellStyle(style2);
			if (kopeg.getValidate() != null) {
				cells9.setCellValue(kopeg.getValidate());
			}

			Cell cells10 = rows.createCell(10);
			cells10.setCellStyle(style2);
			if (kopeg.getKodeKopeg() != null) {
				cells10.setCellValue(kopeg.getKodeKopeg());
			}

			Cell cells11 = rows.createCell(11);
			cells11.setCellStyle(style2);
			if (kopeg.getNamaKopeg() != null) {
				cells11.setCellValue(kopeg.getNamaKopeg());
			}

		}

		for (int a = 0; a <= 11; a++) {
			sheet.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\Compare\\"
				+ years + "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Compare/" + years
				+ "/" + months + "-Difference.xlsx";

		FileOutputStream fos;
		try {
			fos = new FileOutputStream(fileName);
			workbook.write(fos);
			fos.close();
		} catch (Exception e) {
		}

		String type = "compare";
		String statuss = "success";
		Date date = new Date();

		FileStatus fileStatus = new FileStatus(kodeKopeg, type, months, years,
				statuss, fileName, user, date);
		fileStatusDao.saveOrUpdate(fileStatus);

		return "SUCCESS COMPARE DATA";
	}

	public void checkCompareAngsuranTetap(List<Kopeg> listMerge, String years,
			String months, String fileName, String kodeKopeg, String user) {

		List<Kopeg> listCompare = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					String akhirs = null;
					double angsuran = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 4:
									awals = cell.getStringCellValue();
									break;
								case 5:
									akhirs = cell.getStringCellValue();
									break;
								case 8:
									angsuran = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);
					Date akhir = dateParsing(akhirs);

					Kopeg kopeg = new Kopeg(nik, awal, akhir, angsuran);
					listCompare.add(kopeg);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("CompareAngsuranTetap NOT FOUND.");
		}

		for (int i = 0; i < listCompare.size(); i++) {

			listAllCompare.add(listCompare.get(i));
			for (int j = 0; j < listMerge.size(); j++) {

				if (listCompare.get(i).getNik()
						.equals(listMerge.get(j).getNik())) {

					boolean status = true;
					for (int k = 0; k < listDifference.size(); k++) {
						if (listDifference.get(k).getNik()
								.equals(listMerge.get(j).getNik())) {
							status = false;
						}
					}
					if ((status == true)
							&& (((listCompare.get(i).getAngsuran() != listMerge
									.get(j).getAngsuran())) || (listCompare
									.get(i).getAkhir() != listMerge.get(j)
									.getAkhir()))) {
						// System.out.println("Angsuran : "
						// + listCompare.get(i).getAngsuran() + " | "
						// + listMerge.get(j).getAngsuran());
						listDifference.add(listMerge.get(j));
					}
				}
			}
		}
	}

	public void checkCompareSimpananTetap(List<Kopeg> listMerge, String years,
			String months, String fileName, String kodeKopeg, String user) {

		List<Kopeg> listCompare = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					String akhirs = null;
					double simpanan = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 4:
									awals = cell.getStringCellValue();
									break;
								case 5:
									akhirs = cell.getStringCellValue();
									break;
								case 8:
									simpanan = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);
					Date akhir = dateParsing(akhirs);

					Kopeg kopeg = new Kopeg(nik, awal, akhir, simpanan);
					listCompare.add(kopeg);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("CompareSimpananTetap NOT FOUND.");
		}

		for (int i = 0; i < listCompare.size(); i++) {

			listAllCompare.add(listCompare.get(i));
			for (int j = 0; j < listMerge.size(); j++) {

				if (listCompare.get(i).getNik()
						.equals(listMerge.get(j).getNik())) {

					boolean status = true;
					for (int k = 0; k < listDifference.size(); k++) {
						if (listDifference.get(k).getNik()
								.equals(listMerge.get(j).getNik())) {
							status = false;
						}
					}
					if ((status == true)
							&& ((listCompare.get(i).getAngsuran() != listMerge
									.get(j).getSimpanan()))) {
						// System.out.println("Simpanan : "
						// + listCompare.get(i).getAngsuran() + " | "
						// + listMerge.get(j).getSimpanan());
						listDifference.add(listMerge.get(j));
					}
				}
			}
		}
	}

	public void checkCompareAngsuranLainLain(List<Kopeg> listMerge,
			String years, String months, String fileName, String kodeKopeg,
			String user) {

		List<Kopeg> listCompare = new ArrayList<Kopeg>();

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					double lainnya = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 3:
									awals = cell.getStringCellValue();
									break;
								case 5:
									lainnya = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);

					Kopeg kopeg = new Kopeg(nik, awal, lainnya);
					listCompare.add(kopeg);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("CompareAngsuranLainLain NOT FOUND.");
		}

		for (int i = 0; i < listCompare.size(); i++) {

			listAllCompare.add(listCompare.get(i));
			for (int j = 0; j < listMerge.size(); j++) {

				if (listCompare.get(i).getNik()
						.equals(listMerge.get(j).getNik())) {

					boolean status = true;
					for (int k = 0; k < listDifference.size(); k++) {
						if (listDifference.get(k).getNik()
								.equals(listMerge.get(j).getNik())) {
							status = false;
						}
					}
					if ((status == true)
							&& ((listCompare.get(i).getLainnya() != listMerge
									.get(j).getLainnya()))) {
						// System.out.println("Lainnya : "
						// + listCompare.get(i).getLainnya() + " | "
						// + listMerge.get(j).getLainnya());
						listDifference.add(listMerge.get(j));
					}
				}
			}
		}
	}

	public Date dateParsing(String s) {

		int tanggal = Integer.parseInt(s.substring(0, 1));
		int bulan = Integer.parseInt(s.substring(3, 4));
		int tahun = Integer.parseInt(s.substring(6, 9));

		Date date = new Date();
		date.setDate(tanggal);
		date.setMonth(bulan);
		date.setYear(tahun);

		return date;

	}

	public void checkDifferenceAllCompare(List<Kopeg> listMerge) {

		boolean status;

		// IF KOPEG IN LISTMERGE NOT IN LISTALLCOMPARE
		for (int i = 0; i < listMerge.size(); i++) {

			status = true;
			for (int j = 0; j < listAllCompare.size(); j++) {

				if (listMerge.get(i).getNik()
						.equals(listAllCompare.get(j).getNik())) {
					status = false;
				}
			}

			if (status == true) {
				// System.out.println(listMerge.get(i).getNik());
				listDifference.add(listMerge.get(i));
			}
		}

	}

	public String validationReportSimpananTetap(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					String kodeKopegs = "";
					String namaKopeg = "";
					double simpanan = 0.0;
					double payrollSimpanan = 0.0;
					double angsuran = 0.0;
					double payrollAngsuran = 0.0;
					Date awal = null;
					Date akhir = null;
					double lainnya = 0.0;
					double payrollLainnya = 0.0;
					double total = 0.0;
					double payrollTotal = 0.0;
					double deviasi = 0.0;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 10:
								payrollAngsuran = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {

							System.out
									.println("Failed to upload. FORMAT DATA REPORT SIMPANAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

							return "false";
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA REPORT SIMPANAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public String validationReportAngsuranTetap(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					String kodeKopegs = "";
					String namaKopeg = "";
					double simpanan = 0.0;
					double payrollSimpanan = 0.0;
					double angsuran = 0.0;
					double payrollAngsuran = 0.0;
					Date awal = null;
					Date akhir = null;
					double lainnya = 0.0;
					double payrollLainnya = 0.0;
					double total = 0.0;
					double payrollTotal = 0.0;
					double deviasi = 0.0;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 10:
								payrollAngsuran = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
							System.out
									.println("Failed to upload. FORMAT DATA REPORT ANGSURAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

							return "false";
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA REPORT ANGSURAN TETAP IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public String validationReportAngsuranLainLain(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					String kodeKopegs = "";
					String namaKopeg = "";
					double simpanan = 0.0;
					double payrollSimpanan = 0.0;
					double angsuran = 0.0;
					double payrollAngsuran = 0.0;
					Date awal = null;
					Date akhir = null;
					double lainnya = 0.0;
					double payrollLainnya = 0.0;
					double total = 0.0;
					double payrollTotal = 0.0;
					double deviasi = 0.0;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 7:
								payrollLainnya = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
							System.out
									.println("Failed to upload. FORMAT DATA REPORT ANGSURAN LAIN LAIN IS NOT MATCH WITH DEFAULT FORMAT.");

							return "false";
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA REPORT ANGSURAN LAIN LAIN IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public List<FileStatus> findFinalReport(String month, String year) {

		return fileStatusDao.findFinalReport(month, year);
	}

	public String getSplitFinalReport(String years, String months, String path,
			String kodeKopeg, String user) {

		listFinalReport = new ArrayList<FinalReport>();

		File directories = new File(path);
		File[] fList = directories.listFiles();
		String dir = "";

		if (fList.length == 0) {
			dir = "null";
		}

		if (dir.equals("null")) {

			String type = "split";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKopeg, type, months,
					years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			return "Failed to split. FILE FINAL REPORT IS NOT FOUND IN DIRECTORY.";
		}

		for (File file : fList) {

			String fileName = path + "" + file.getName();

			if (file.getName().startsWith("ReportAngsuranTetap")) {
				getReportAngsuranTetap(years, months, fileName, kodeKopeg, user);
			} else {
				if (file.getName().startsWith("ReportSimpananTetap")) {
					getReportSimpananTetap(years, months, fileName, kodeKopeg,
							user);
				} else {
					if (file.getName().startsWith("ReportAngsuranLainLain")) {
						getReportAngsuranLainLain(years, months, fileName,
								kodeKopeg, user);
					}
				}
			}

		}

		// OPEN LIST MERGE ALL DETAIL
		List<Kopeg> listMergeDetail = getMergeDetail(years, months);

		int totalKopeg = listKopegDao.getTotalKopeg();

		ArrayList<FinalReport>[] group = (ArrayList<FinalReport>[]) new ArrayList[totalKopeg];

		for (int t = 0; t < totalKopeg; t++) {
			group[t] = new ArrayList<FinalReport>();
		}

		boolean[] flag = new boolean[listMergeDetail.size()];

		for (int b = 0; b < listMergeDetail.size(); b++) {
			flag[b] = true;
		}

		for (int a = 0; a < listFinalReport.size(); a++) {

			double payrollSimpananSisa = listFinalReport.get(a)
					.getPayrollSimpanan();
			double payrollAngsuranSisa = listFinalReport.get(a)
					.getPayrollAngsuran();
			double payrollLainnyaSisa = listFinalReport.get(a)
					.getPayrollLainnya();

			int index = -1;
			String kodeKopegs = "";

			String nik = "";
			String nama = "";
			String namaKopeg = "";
			double simpanan = 0.0;
			double payrollSimpanan = 0.0;
			double angsuran = 0.0;
			double payrollAngsuran = 0.0;
			Date akhir = null;
			double lainnya = 0.0;
			double payrollLainnya = 0.0;
			double total = 0.0;
			double payrollTotal = 0.0;
			double deviasi = 0.0;
			String keterangan = "";

			int counter = 0;
			List<String> idx = new ArrayList<String>();

			for (int b = 0; b < listMergeDetail.size(); b++) {

				if (listFinalReport.get(a).getNik()
						.equals(listMergeDetail.get(b).getNik())) {
					idx.add("" + b);
					counter++;
				}
			}

			List<Kopeg> tempMergeDetail = new ArrayList<Kopeg>();

			int counterGlobal = 0;
			boolean loop = true;
			while ((counterGlobal < counter) && (loop == true)) {

				Date awal = new Date();
				awal.setDate(1);
				awal.setMonth(1);
				awal.setYear(10000);

				index = -1;

				for (int c = 0; c < counter; c++) {

					if ((flag[Integer.parseInt(idx.get(c))] == true)
							&& (listMergeDetail.get(
									Integer.parseInt(idx.get(c))).getAwal() == null)) {
						flag[Integer.parseInt(idx.get(c))] = false;
						tempMergeDetail.add(listMergeDetail.get(Integer
								.parseInt(idx.get(c))));
					} else {
						if ((flag[Integer.parseInt(idx.get(c))] == true)
								&& (listMergeDetail
										.get(Integer.parseInt(idx.get(c)))
										.getAwal().compareTo(awal) < 0)) {

							flag[Integer.parseInt(idx.get(c))] = false;
							awal = listMergeDetail.get(
									Integer.parseInt(idx.get(c))).getAwal();
							index = Integer.parseInt(idx.get(c));
						}
					}
				}

				if (index == -1) {

					awal = null;

					for (int aa = 0; aa < tempMergeDetail.size(); aa++) {

						kodeKopegs = tempMergeDetail.get(aa).getKodeKopeg();

						nik = tempMergeDetail.get(aa).getNik();
						nama = tempMergeDetail.get(aa).getNama();
						namaKopeg = tempMergeDetail.get(aa).getNamaKopeg();
						simpanan = tempMergeDetail.get(aa).getSimpanan();
						angsuran = tempMergeDetail.get(aa).getAngsuran();
						akhir = tempMergeDetail.get(aa).getAkhir();
						lainnya = tempMergeDetail.get(aa).getLainnya();

						// KOPEG YG BEGDA NYA TERAKHIR, MAKA PAYROLL DIISI
						// SISANYA
						if (aa == (tempMergeDetail.size() - 1)) {
							payrollSimpanan = payrollSimpananSisa;
							payrollAngsuran = payrollAngsuranSisa;
							payrollLainnya = payrollLainnyaSisa;
						} else {

							if (payrollSimpananSisa > simpanan) {
								payrollSimpanan = simpanan;
								payrollSimpananSisa = payrollSimpananSisa
										- payrollSimpanan;
							} else {
								payrollSimpanan = payrollSimpananSisa;
								payrollSimpananSisa = 0.0;
							}

							if (payrollAngsuranSisa > angsuran) {
								payrollAngsuran = angsuran;
								payrollAngsuranSisa = payrollAngsuranSisa
										- payrollAngsuran;
							} else {
								payrollAngsuran = payrollAngsuranSisa;
								payrollAngsuranSisa = 0.0;
							}

							if (payrollLainnyaSisa > lainnya) {
								payrollLainnya = lainnya;
								payrollLainnyaSisa = payrollLainnyaSisa
										- payrollLainnya;
							} else {
								payrollLainnya = payrollLainnyaSisa;
								payrollLainnyaSisa = 0.0;
							}
						}

						total = simpanan + angsuran + lainnya;
						payrollTotal = payrollSimpanan + payrollAngsuran
								+ payrollLainnya;
						deviasi = total - payrollTotal;

						if (deviasi == 0.0) {
							keterangan = "DIPEKERJAKAN";
						} else {
							keterangan = "";
						}

						FinalReport finalReport = new FinalReport(nik, nama,
								kodeKopegs, namaKopeg, simpanan,
								payrollSimpanan, angsuran, payrollAngsuran,
								awal, akhir, lainnya, payrollLainnya, total,
								payrollTotal, deviasi, keterangan);

						int id = listKopegDao.getId(kodeKopegs);
						group[id].add(finalReport);
					}

					loop = false;
				}

				else {

					kodeKopegs = listMergeDetail.get(index).getKodeKopeg();

					nik = listMergeDetail.get(index).getNik();
					nama = listMergeDetail.get(index).getNama();
					namaKopeg = listMergeDetail.get(index).getNamaKopeg();
					simpanan = listMergeDetail.get(index).getSimpanan();
					angsuran = listMergeDetail.get(index).getAngsuran();
					akhir = listMergeDetail.get(index).getAkhir();
					lainnya = listMergeDetail.get(index).getLainnya();

					// KOPEG YG BEGDA NYA TERAKHIR, MAKA PAYROLL DIISI SISANYA
					if (counterGlobal == (counter - 1)) {
						payrollSimpanan = payrollSimpananSisa;
						payrollAngsuran = payrollAngsuranSisa;
						payrollLainnya = payrollLainnyaSisa;
					} else {

						if (payrollSimpananSisa > simpanan) {
							payrollSimpanan = simpanan;
							payrollSimpananSisa = payrollSimpananSisa
									- payrollSimpanan;
						} else {
							payrollSimpanan = payrollSimpananSisa;
							payrollSimpananSisa = 0.0;
						}

						if (payrollAngsuranSisa > angsuran) {
							payrollAngsuran = angsuran;
							payrollAngsuranSisa = payrollAngsuranSisa
									- payrollAngsuran;
						} else {
							payrollAngsuran = payrollAngsuranSisa;
							payrollAngsuranSisa = 0.0;
						}

						if (payrollLainnyaSisa > lainnya) {
							payrollLainnya = lainnya;
							payrollLainnyaSisa = payrollLainnyaSisa
									- payrollLainnya;
						} else {
							payrollLainnya = payrollLainnyaSisa;
							payrollLainnyaSisa = 0.0;
						}
					}

					total = simpanan + angsuran + lainnya;
					payrollTotal = payrollSimpanan + payrollAngsuran
							+ payrollLainnya;
					deviasi = total - payrollTotal;

					if (deviasi == 0.0) {
						keterangan = "DIPEKERJAKAN";
					} else {
						keterangan = "";
					}

					FinalReport finalReport = new FinalReport(nik, nama,
							kodeKopegs, namaKopeg, simpanan, payrollSimpanan,
							angsuran, payrollAngsuran, awal, akhir, lainnya,
							payrollLainnya, total, payrollTotal, deviasi,
							keterangan);

					int id = listKopegDao.getId(kodeKopegs);
					group[id].add(finalReport);

					loop = true;
				}

			}
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\SplitFinalReport\\"
				+ years + "\\" + months;

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}

		for (int z = 0; z < group.length; z++) {

			if (group[z].size() != 0) {

				Workbook workbook = new XSSFWorkbook();
				Sheet sheet = workbook.createSheet("Sheet1");

				// HEADER
				CellStyle style = workbook.createCellStyle();
				style.setFillForegroundColor(IndexedColors.YELLOW.index);
				style.setFillPattern(CellStyle.SOLID_FOREGROUND);
				style.setAlignment(CellStyle.ALIGN_CENTER);
				style.setBorderRight(CellStyle.BORDER_THIN);
				style.setBorderLeft(CellStyle.BORDER_THIN);
				style.setBorderTop(CellStyle.BORDER_THIN);
				style.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont font = (XSSFFont) workbook.createFont();
				font.setBold(true);
				style.setFont(font);

				// DATA
				CellStyle style2 = workbook.createCellStyle();
				style2.setBorderRight(CellStyle.BORDER_THIN);
				style2.setBorderLeft(CellStyle.BORDER_THIN);
				style2.setBorderTop(CellStyle.BORDER_THIN);
				style2.setBorderBottom(CellStyle.BORDER_THIN);

				Row row = sheet.createRow(0);

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("NAMA");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("KODE KOPEG");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("NAMA KOPEG");
				cell3.setCellStyle(style);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("SIMPANAN");
				cell4.setCellStyle(style);

				Cell cell5 = row.createCell(5);
				cell5.setCellValue("PAYROLL SIMPANAN");
				cell5.setCellStyle(style);

				Cell cell6 = row.createCell(6);
				cell6.setCellValue("ANGSURAN");
				cell6.setCellStyle(style);

				Cell cell7 = row.createCell(7);
				cell7.setCellValue("PAYROLL ANGSURAN");
				cell7.setCellStyle(style);

				Cell cell8 = row.createCell(8);
				cell8.setCellValue("AWAL");
				cell8.setCellStyle(style);

				Cell cell9 = row.createCell(9);
				cell9.setCellValue("AKHIR");
				cell9.setCellStyle(style);

				Cell cell10 = row.createCell(10);
				cell10.setCellValue("LAINNYA");
				cell10.setCellStyle(style);

				Cell cell11 = row.createCell(11);
				cell11.setCellValue("PAYROLL LAINNYA");
				cell11.setCellStyle(style);

				Cell cell12 = row.createCell(12);
				cell12.setCellValue("TOTAL");
				cell12.setCellStyle(style);

				Cell cell13 = row.createCell(13);
				cell13.setCellValue("PAYROLL TOTAL");
				cell13.setCellStyle(style);

				Cell cell14 = row.createCell(14);
				cell14.setCellValue("DEVIASI");
				cell14.setCellStyle(style);

				Cell cell15 = row.createCell(15);
				cell15.setCellValue("KETERANGAN");
				cell15.setCellStyle(style);

				Iterator<FinalReport> iterator = group[z].iterator();

				int rowIndex = 1;
				while (iterator.hasNext()) {

					FinalReport finalReport = iterator.next();

					Row rows = sheet.createRow(rowIndex++);

					Cell cells0 = rows.createCell(0);
					cells0.setCellStyle(style2);
					if (finalReport.getNik() != null) {
						cells0.setCellValue(finalReport.getNik());
					}

					Cell cells1 = rows.createCell(1);
					cells1.setCellStyle(style2);
					if (finalReport.getNama() != null) {
						cells1.setCellValue(finalReport.getNama());
					}

					Cell cells2 = rows.createCell(2);
					cells2.setCellStyle(style2);
					if (finalReport.getKodeKopeg() != null) {
						cells2.setCellValue(finalReport.getKodeKopeg());
					}

					Cell cells3 = rows.createCell(3);
					cells3.setCellStyle(style2);
					if (finalReport.getNamaKopeg() != null) {
						cells3.setCellValue(finalReport.getNamaKopeg());
					}

					Cell cells4 = rows.createCell(4);
					cells4.setCellValue(finalReport.getSimpanan());
					cells4.setCellStyle(style2);

					Cell cells5 = rows.createCell(5);
					cells5.setCellValue(finalReport.getPayrollSimpanan());
					cells5.setCellStyle(style2);

					Cell cells6 = rows.createCell(6);
					cells6.setCellValue(finalReport.getAngsuran());
					cells6.setCellStyle(style2);

					Cell cells7 = rows.createCell(7);
					cells7.setCellValue(finalReport.getPayrollAngsuran());
					cells7.setCellStyle(style2);

					Cell cells8 = rows.createCell(8);
					cells8.setCellStyle(style2);
					if (finalReport.getAwal() != null) {
						cells8.setCellValue(finalReport.getAwal());
					}

					Cell cells9 = rows.createCell(9);
					cells9.setCellStyle(style2);
					if (finalReport.getAkhir() != null) {
						cells9.setCellValue(finalReport.getAkhir());
					}

					Cell cells10 = rows.createCell(10);
					cells10.setCellValue(finalReport.getLainnya());
					cells10.setCellStyle(style2);

					Cell cells11 = rows.createCell(11);
					cells11.setCellValue(finalReport.getPayrollLainnya());
					cells11.setCellStyle(style2);

					Cell cells12 = rows.createCell(12);
					cells12.setCellValue(finalReport.getTotal());
					cells12.setCellStyle(style2);

					Cell cells13 = rows.createCell(13);
					cells13.setCellValue(finalReport.getPayrollTotal());
					cells13.setCellStyle(style2);

					Cell cells14 = rows.createCell(14);
					cells14.setCellValue(finalReport.getDeviasi());
					cells14.setCellStyle(style2);

					Cell cells15 = rows.createCell(15);
					cells15.setCellStyle(style2);
					if (finalReport.getKeterangan() != null) {
						cells15.setCellValue(finalReport.getKeterangan());
					}
				}

				for (int a = 0; a <= 15; a++) {
					sheet.autoSizeColumn(a);
				}

				String kodeKopegss = listKopegDao.getKodeKopegss(z);

				String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/SplitFinalReport/"
						+ years
						+ "/"
						+ months
						+ "/"
						+ kodeKopegss
						+ "-FinalReport.xlsx";

				FileOutputStream fos;
				try {
					fos = new FileOutputStream(fileName);
					workbook.write(fos);
					fos.close();
				} catch (Exception e) {
				}

			}
		}

		String type = "split";
		String statuss = "success";
		Date date = new Date();
		String directory2 = "C:/DIRECTORY_BILLING_MANAGEMENT/SplitFinalReport/"
				+ years + "/" + months + "/";

		FileStatus fileStatus = new FileStatus(kodeKopeg, type, months, years,
				statuss, directory2, user, date);
		fileStatusDao.saveOrUpdate(fileStatus);

		return "SUCCESS SPLIT FINAL REPORT";
	}

	public void getReportAngsuranTetap(String years, String months,
			String fileName, String kodeKopeg, String user) {

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					String kodeKopegs = "";
					String namaKopeg = "";
					double simpanan = 0.0;
					double payrollSimpanan = 0.0;
					double angsuran = 0.0;
					double payrollAngsuran = 0.0;
					Date awal = null;
					Date akhir = null;
					double lainnya = 0.0;
					double payrollLainnya = 0.0;
					double total = 0.0;
					double payrollTotal = 0.0;
					double deviasi = 0.0;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 4:
								awal = cell.getDateCellValue();
								break;
							case 5:
								akhir = cell.getDateCellValue();
								break;
							case 10:
								payrollAngsuran = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					boolean status = true;
					int index = 0;
					for (int k = 0; k < listFinalReport.size(); k++) {
						if (listFinalReport.get(k).getNik().equals(nik)) {
							status = false;
							index = k;
						}
					}
					if (status == false) {
						listFinalReport.get(index).setPayrollAngsuran(
								payrollAngsuran);
						listFinalReport.get(index).setAwal(awal);
						listFinalReport.get(index).setAkhir(akhir);
					} else {
						FinalReport finalReport = new FinalReport(nik, nama,
								kodeKopegs, namaKopeg, simpanan,
								payrollSimpanan, angsuran, payrollAngsuran,
								awal, akhir, lainnya, payrollLainnya, total,
								payrollTotal, deviasi, keterangan);
						listFinalReport.add(finalReport);
					}
				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("ReportAngsuranTetap NOT FOUND.");
		}

	}

	public void getReportSimpananTetap(String years, String months,
			String fileName, String kodeKopeg, String user) {

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					String kodeKopegs = "";
					String namaKopeg = "";
					double simpanan = 0.0;
					double payrollSimpanan = 0.0;
					double angsuran = 0.0;
					double payrollAngsuran = 0.0;
					Date awal = null;
					Date akhir = null;
					double lainnya = 0.0;
					double payrollLainnya = 0.0;
					double total = 0.0;
					double payrollTotal = 0.0;
					double deviasi = 0.0;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 10:
								payrollSimpanan = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					boolean status = true;
					int index = 0;
					for (int k = 0; k < listFinalReport.size(); k++) {
						if (listFinalReport.get(k).getNik().equals(nik)) {
							status = false;
							index = k;
						}
					}
					if (status == false) {
						listFinalReport.get(index).setPayrollSimpanan(
								payrollSimpanan);
					} else {
						FinalReport finalReport = new FinalReport(nik, nama,
								kodeKopegs, namaKopeg, simpanan,
								payrollSimpanan, angsuran, payrollAngsuran,
								awal, akhir, lainnya, payrollLainnya, total,
								payrollTotal, deviasi, keterangan);
						listFinalReport.add(finalReport);
					}

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("ReportSimpananTetap NOT FOUND.");
		}
	}

	public void getReportAngsuranLainLain(String years, String months,
			String fileName, String kodeKopeg, String user) {

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					String kodeKopegs = "";
					String namaKopeg = "";
					double simpanan = 0.0;
					double payrollSimpanan = 0.0;
					double angsuran = 0.0;
					double payrollAngsuran = 0.0;
					Date awal = null;
					Date akhir = null;
					double lainnya = 0.0;
					double payrollLainnya = 0.0;
					double total = 0.0;
					double payrollTotal = 0.0;
					double deviasi = 0.0;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 7:
								payrollLainnya = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					boolean status = true;
					int index = 0;
					for (int k = 0; k < listFinalReport.size(); k++) {
						if (listFinalReport.get(k).getNik().equals(nik)) {
							status = false;
							index = k;
						}
					}
					if (status == false) {
						listFinalReport.get(index).setPayrollLainnya(
								payrollLainnya);
					} else {
						FinalReport finalReport = new FinalReport(nik, nama,
								kodeKopegs, namaKopeg, simpanan,
								payrollSimpanan, angsuran, payrollAngsuran,
								awal, akhir, lainnya, payrollLainnya, total,
								payrollTotal, deviasi, keterangan);
						listFinalReport.add(finalReport);
					}

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("ReportAngsuranLainLain NOT FOUND.");
		}
	}

	public List<Kopeg> getMergeDetail(String years, String months) {

		List<Kopeg> listMerge = new ArrayList<Kopeg>();

		try {

			String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/Merge/" + years
					+ "/" + months + "/" + "SummarizeDetail.xlsx";
			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String nama = "";
					double simpanan = 0.0;
					double angsuran = 0.0;
					double lainnya = 0.0;
					double total = 0.0;
					Date awal = null;
					Date akhir = null;
					String keterangan = "";
					String validate = "";
					String kodeKopegs = "";
					String namaKopeg = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 2:
								simpanan = cell.getNumericCellValue();
								break;
							case 3:
								angsuran = cell.getNumericCellValue();
								break;
							case 4:
								lainnya = cell.getNumericCellValue();
								break;
							case 5:
								total = cell.getNumericCellValue();
								break;
							case 6:
								awal = cell.getDateCellValue();
								break;
							case 7:
								akhir = cell.getDateCellValue();
								break;
							case 8:
								keterangan = cell.getStringCellValue();
								break;
							case 9:
								validate = cell.getStringCellValue();
								break;
							case 10:
								kodeKopegs = cell.getStringCellValue();
								break;
							case 11:
								namaKopeg = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					Kopeg kopeg = new Kopeg(nik, nama, simpanan, angsuran,
							lainnya, total, awal, akhir, keterangan, validate,
							kodeKopegs, namaKopeg);
					listMerge.add(kopeg);

				}// IF ROW NUMBER >=1 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
		}

		return listMerge;
	}

	// KOPTEL PRODUCT
	public List<KoptelProduct> readKoptelProduct(MultipartFile file,
			String path, String kodeKoptelProduct, String months, String years,
			String user, String product) {

		List<KoptelProduct> listKoptelProduct = new ArrayList<KoptelProduct>();
		boolean deleteFile = false;

		try {

			FileInputStream fis = new FileInputStream(path);

			Workbook workbook = null;

			if (path.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (path.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			ROWITERATOR: while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 3) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;
					Date begda = null;
					Date enda = null;
					String keterangan = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {

						Cell cell = cellIterator.next();

						String temp = "";
						switch (cell.getCellType()) {
						case Cell.CELL_TYPE_STRING:
							temp = cell.getStringCellValue();
						}

						if (temp.equals("GRAND TOTAL")) {
							break ROWITERATOR;
						} else {
							if (cell.getColumnIndex() >= 1) {
								try {
									switch (cell.getColumnIndex()) {
									case 1:
										int niks = (int) cell
												.getNumericCellValue();
										nik = "" + niks;
										break;
									case 2:
										nama = cell.getStringCellValue();
										break;
									case 3:
										jumlah = cell.getNumericCellValue();
										break;
									case 4:
										begda = cell.getDateCellValue();
										break;
									case 5:
										enda = cell.getDateCellValue();
										break;
									case 6:
										keterangan = cell.getStringCellValue();
										break;
									}
								} catch (Exception e) {
								}
							}
						}
					}// CELL ITERATOR WHILE END

					String validate = employeeDao.checkEmployeeToDB(nik, nama);

					if (validate.equals("false")) {
						deleteFile = true;
					}

					String namaKoptelProduct = listKoptelProductDao
							.getNamaKoptelProduct(kodeKoptelProduct);

					KoptelProduct koptelProduct = new KoptelProduct(nik, nama,
							jumlah, begda, enda, keterangan, validate,
							kodeKoptelProduct, namaKoptelProduct);
					listKoptelProduct.add(koptelProduct);

				}// IF ROW NUMBER >=3 END

			}// ROW ITERATOR WHILE END

			String type = "upload" + product;
			String status = "success";
			Date date = new Date();

			// FAILED IF DATA EMPLOYEE IS INCORRECT
			if (deleteFile == true) {
				File filed = new File(path);
				filed.delete();
				status = "failed";
				path = "-";
			}

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			fis.close();

		} catch (Exception e) {

			String type = "upload" + product;
			String status = "failed";
			Date date = new Date();

			// FAILED IF FORMAT DATA KOPTEL PRODUCT IS NOT MATCH WITH DEFAULT
			// FORMAT
			File filed = new File(path);
			filed.delete();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			listKoptelProduct = null;
			System.out
					.println("Failed to upload. FORMAT DATA KOPTEL PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");
		}

		return listKoptelProduct;
	}

	public List<Employee> warningPensiunKoptelProduct(
			List<KoptelProduct> listKoptelProduct) {

		List<Employee> listEmp = new ArrayList<Employee>();

		Date date = new Date();

		Calendar cal = Calendar.getInstance();
		int daysToDecrement = -60; // 2 month

		for (int i = 0; i < listKoptelProduct.size(); i++) {

			Employee emp = employeeDao.findById(listKoptelProduct.get(i)
					.getNik());

			Date pensiun = emp.getTglPensiunNorm();
			cal.setTime(pensiun);
			cal.add(Calendar.DATE, daysToDecrement);
			pensiun = cal.getTime();

			if (pensiun.before(date)) {
				listEmp.add(emp);
			}
		}

		return listEmp;
	}

	public List<FileStatus> findByDateProduct(String month, String year) {

		return fileStatusDao.findByDateProduct(month, year);
	}

	public String mergeProduct(String years, String months, String path,
			String kodeKoptelProduct, String user) {

		List<KoptelProduct> listAngsuranTetap = new ArrayList<KoptelProduct>();
		List<KoptelProduct> listAngsuranLainLain = new ArrayList<KoptelProduct>();

		try {

			File directory = new File(path);
			File[] fList = directory.listFiles();

			for (File file : fList) {

				String fileName = path + "" + file.getName();
				FileInputStream fis = new FileInputStream(fileName);
				Workbook workbook = null;

				if (fileName.toLowerCase().endsWith("xlsx")) {
					workbook = new XSSFWorkbook(fis);
				} else if (path.toLowerCase().endsWith("xls")) {
					workbook = new HSSFWorkbook(fis);
				}

				Sheet sheet = workbook.getSheet("Sheet1");

				Iterator<Row> rowIterator = sheet.iterator();
				ROWITERATOR: while (rowIterator.hasNext()) {

					Row row = rowIterator.next();

					if (row.getRowNum() >= 3) {

						String nik = "";
						String nama = "";
						double jumlah = 0.0;
						Date begda = null;
						Date enda = null;
						String keterangan = "";

						Iterator<Cell> cellIterator = row.cellIterator();

						while (cellIterator.hasNext()) {
							Cell cell = cellIterator.next();

							String temp = "";
							switch (cell.getCellType()) {
							case Cell.CELL_TYPE_STRING:
								temp = cell.getStringCellValue();
							}

							if (temp.equals("GRAND TOTAL")) {
								break ROWITERATOR;
							} else {
								if (cell.getColumnIndex() >= 1) {
									try {
										switch (cell.getColumnIndex()) {
										case 1:
											int niks = (int) cell
													.getNumericCellValue();
											nik = "" + niks;
											break;
										case 2:
											nama = cell.getStringCellValue();
											break;
										case 3:
											jumlah = cell.getNumericCellValue();
											break;
										case 4:
											begda = cell.getDateCellValue();
											break;
										case 5:
											enda = cell.getDateCellValue();
											break;
										case 6:
											keterangan = cell
													.getStringCellValue();
											break;
										}
									} catch (Exception e) {
									}
								}
							}
						}// CELL ITERATOR WHILE END

						if (file.getName().startsWith("AngsuranTetap")) {

							String kodeKoptelProducts = file.getName()
									.substring(14, 20);
							String namaKoptelProduct = listKoptelProductDao
									.getNamaKoptelProduct(kodeKoptelProducts);

							KoptelProduct koptelProduct = new KoptelProduct(
									nik, nama, jumlah, begda, enda, keterangan,
									"true", kodeKoptelProducts,
									namaKoptelProduct);
							listAngsuranTetap.add(koptelProduct);

						} else {
							if (file.getName().startsWith("AngsuranLainLain")) {

								String kodeKoptelProducts = file.getName()
										.substring(17, 23);
								String namaKoptelProduct = listKoptelProductDao
										.getNamaKoptelProduct(kodeKoptelProducts);

								KoptelProduct koptelProduct = new KoptelProduct(
										nik, nama, jumlah, begda, enda,
										keterangan, "true", kodeKoptelProducts,
										namaKoptelProduct);
								listAngsuranLainLain.add(koptelProduct);
							}
						}

					}// IF ROW NUMBER >=3 END
				}// ROW ITERATOR WHILE END

				fis.close();
			}

			if (listAngsuranTetap.size() != 0) {

				List<KoptelProduct> listAngsuranTetapFinal = new ArrayList<KoptelProduct>();

				// MERGE WHICH SAME NIK
				for (int i = 0; i < listAngsuranTetap.size(); i++) {

					String nik = listAngsuranTetap.get(i).getNik();
					String nama = listAngsuranTetap.get(i).getNama();
					double jumlah = listAngsuranTetap.get(i).getJumlah();
					Date begda = listAngsuranTetap.get(i).getBegda();
					Date enda = listAngsuranTetap.get(i).getEnda();
					String keterangan = listAngsuranTetap.get(i)
							.getKeterangan();
					String validate = listAngsuranTetap.get(i).getValidate();
					String kodeKoptelProducts = "merge";
					String namaKoptelProduct = "merge";

					boolean statusFinal = false;
					int indexFinal = 0;

					for (int j = 0; j < listAngsuranTetapFinal.size(); j++) {

						if (nik.equals(listAngsuranTetapFinal.get(j).getNik())) {

							statusFinal = true;
							indexFinal = j;
						}
					}

					if (statusFinal == true) {

						jumlah = jumlah
								+ listAngsuranTetap.get(indexFinal).getJumlah();

						// UNTUK KOPEG PRODUK

						// BEGDA YG BULANNYA LEBIH BESAR YG DIAMBIL
						if (listAngsuranTetap.get(indexFinal).getBegda()
								.compareTo(begda) > 0) {
							begda = listAngsuranTetap.get(indexFinal)
									.getBegda();
						}

						// ENDA YG BULANNYA LEBIH KECIL YG DIAMBIL
						if (listAngsuranTetap.get(indexFinal).getEnda()
								.compareTo(enda) < 0) {
							enda = listAngsuranTetap.get(indexFinal).getEnda();
						}

						KoptelProduct koptelProduct = new KoptelProduct(nik,
								nama, jumlah, begda, enda, keterangan,
								validate, kodeKoptelProducts, namaKoptelProduct);

						listAngsuranTetapFinal.set(indexFinal, koptelProduct);

					} else {

						KoptelProduct koptelProduct = new KoptelProduct(nik,
								nama, jumlah, begda, enda, keterangan,
								validate, kodeKoptelProducts, namaKoptelProduct);
						listAngsuranTetapFinal.add(koptelProduct);
					}
				}

				Workbook workbook = new XSSFWorkbook();
				Sheet sheet = workbook.createSheet("Sheet1");

				// HEADER
				CellStyle style = workbook.createCellStyle();
				style.setFillForegroundColor(IndexedColors.YELLOW.index);
				style.setFillPattern(CellStyle.SOLID_FOREGROUND);
				style.setAlignment(CellStyle.ALIGN_CENTER);
				style.setBorderRight(CellStyle.BORDER_THIN);
				style.setBorderLeft(CellStyle.BORDER_THIN);
				style.setBorderTop(CellStyle.BORDER_THIN);
				style.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont font = (XSSFFont) workbook.createFont();
				font.setBold(true);
				style.setFont(font);

				// DATA
				CellStyle style2 = workbook.createCellStyle();
				style2.setBorderRight(CellStyle.BORDER_THIN);
				style2.setBorderLeft(CellStyle.BORDER_THIN);
				style2.setBorderTop(CellStyle.BORDER_THIN);
				style2.setBorderBottom(CellStyle.BORDER_THIN);

				Iterator<KoptelProduct> iterator = listAngsuranTetapFinal
						.iterator();

				int rowIndex = 0;
				while (iterator.hasNext()) {

					Row row = sheet.createRow(rowIndex++);

					if (rowIndex == 1) {

						Cell cell0 = row.createCell(0);
						cell0.setCellValue("NIK");
						cell0.setCellStyle(style);

						Cell cell1 = row.createCell(1);
						cell1.setCellValue("NAMA");
						cell1.setCellStyle(style);

						Cell cell2 = row.createCell(2);
						cell2.setCellValue("JUMLAH");
						cell2.setCellStyle(style);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue("BEGDA");
						cell3.setCellStyle(style);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue("ENDA");
						cell4.setCellStyle(style);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue("KETERANGAN");
						cell5.setCellStyle(style);

						Cell cell6 = row.createCell(6);
						cell6.setCellValue("VALIDATE");
						cell6.setCellStyle(style);

						Cell cell7 = row.createCell(7);
						cell7.setCellValue("KODE_KOPTEL_PRODUCT");
						cell7.setCellStyle(style);

						Cell cell8 = row.createCell(8);
						cell8.setCellValue("NAMA_KOPTEL_PRODUCT");
						cell8.setCellStyle(style);
					} else {

						KoptelProduct koptelProduct = iterator.next();

						Cell cell0 = row.createCell(0);
						cell0.setCellStyle(style2);
						if (koptelProduct.getNik() != null) {
							cell0.setCellValue(koptelProduct.getNik());
						}

						Cell cell1 = row.createCell(1);
						cell1.setCellStyle(style2);
						if (koptelProduct.getNama() != null) {
							cell1.setCellValue(koptelProduct.getNama());
						}

						Cell cell2 = row.createCell(2);
						cell2.setCellValue(koptelProduct.getJumlah());
						cell2.setCellStyle(style2);

						Cell cell3 = row.createCell(3);
						cell3.setCellStyle(style2);
						if (koptelProduct.getBegda() != null) {
							cell3.setCellValue(koptelProduct.getBegda());
						}

						Cell cell4 = row.createCell(4);
						cell4.setCellStyle(style2);
						if (koptelProduct.getEnda() != null) {
							cell4.setCellValue(koptelProduct.getEnda());
						}

						Cell cell5 = row.createCell(5);
						cell5.setCellStyle(style2);
						if (koptelProduct.getKeterangan() != null) {
							cell5.setCellValue(koptelProduct.getKeterangan());
						}

						Cell cell6 = row.createCell(6);
						cell6.setCellStyle(style2);
						if (koptelProduct.getValidate() != null) {
							cell6.setCellValue(koptelProduct.getValidate());
						}

						Cell cell7 = row.createCell(7);
						cell7.setCellStyle(style2);
						if (koptelProduct.getKodeKoptelProduct() != null) {
							cell7.setCellValue(koptelProduct
									.getKodeKoptelProduct());
						}

						Cell cell8 = row.createCell(8);
						cell8.setCellStyle(style2);
						if (koptelProduct.getNamaKoptelProduct() != null) {
							cell8.setCellValue(koptelProduct
									.getNamaKoptelProduct());
						}
					}
				}

				for (int a = 0; a <= 8; a++) {
					sheet.autoSizeColumn(a);
				}

				// SUMMARIZE DETAIL
				Workbook workbook2 = new XSSFWorkbook();
				Sheet sheet2 = workbook2.createSheet("Sheet1");

				// HEADER
				CellStyle styles = workbook2.createCellStyle();
				styles.setFillForegroundColor(IndexedColors.YELLOW.index);
				styles.setFillPattern(CellStyle.SOLID_FOREGROUND);
				styles.setAlignment(CellStyle.ALIGN_CENTER);
				styles.setBorderRight(CellStyle.BORDER_THIN);
				styles.setBorderLeft(CellStyle.BORDER_THIN);
				styles.setBorderTop(CellStyle.BORDER_THIN);
				styles.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont fonts = (XSSFFont) workbook2.createFont();
				fonts.setBold(true);
				styles.setFont(fonts);

				// DATA
				CellStyle styles2 = workbook2.createCellStyle();
				styles2.setBorderRight(CellStyle.BORDER_THIN);
				styles2.setBorderLeft(CellStyle.BORDER_THIN);
				styles2.setBorderTop(CellStyle.BORDER_THIN);
				styles2.setBorderBottom(CellStyle.BORDER_THIN);

				Iterator<KoptelProduct> iterator2 = listAngsuranTetap
						.iterator();

				int rowIndex2 = 0;
				while (iterator2.hasNext()) {

					Row row = sheet2.createRow(rowIndex2++);

					if (rowIndex2 == 1) {

						Cell cell0 = row.createCell(0);
						cell0.setCellValue("NIK");
						cell0.setCellStyle(styles);

						Cell cell1 = row.createCell(1);
						cell1.setCellValue("NAMA");
						cell1.setCellStyle(styles);

						Cell cell2 = row.createCell(2);
						cell2.setCellValue("JUMLAH");
						cell2.setCellStyle(styles);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue("BEGDA");
						cell3.setCellStyle(styles);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue("ENDA");
						cell4.setCellStyle(styles);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue("KETERANGAN");
						cell5.setCellStyle(styles);

						Cell cell6 = row.createCell(6);
						cell6.setCellValue("VALIDATE");
						cell6.setCellStyle(styles);

						Cell cell7 = row.createCell(7);
						cell7.setCellValue("KODE_KOPTEL_PRODUCT");
						cell7.setCellStyle(styles);

						Cell cell8 = row.createCell(8);
						cell8.setCellValue("NAMA_KOPTEL_PRODUCT");
						cell8.setCellStyle(styles);

					} else {

						KoptelProduct koptelProduct = iterator2.next();

						Cell cell0 = row.createCell(0);
						cell0.setCellStyle(styles2);
						if (koptelProduct.getNik() != null) {
							cell0.setCellValue(koptelProduct.getNik());
						}

						Cell cell1 = row.createCell(1);
						cell1.setCellStyle(styles2);
						if (koptelProduct.getNama() != null) {
							cell1.setCellValue(koptelProduct.getNama());
						}

						Cell cell2 = row.createCell(2);
						cell2.setCellValue(koptelProduct.getJumlah());
						cell2.setCellStyle(styles2);

						Cell cell3 = row.createCell(3);
						cell3.setCellStyle(styles2);
						if (koptelProduct.getBegda() != null) {
							cell3.setCellValue(koptelProduct.getBegda());
						}

						Cell cell4 = row.createCell(4);
						cell4.setCellStyle(styles2);
						if (koptelProduct.getEnda() != null) {
							cell4.setCellValue(koptelProduct.getEnda());
						}

						Cell cell5 = row.createCell(5);
						cell5.setCellStyle(styles2);
						if (koptelProduct.getKeterangan() != null) {
							cell5.setCellValue(koptelProduct.getKeterangan());
						}

						Cell cell6 = row.createCell(6);
						cell6.setCellStyle(styles2);
						if (koptelProduct.getValidate() != null) {
							cell6.setCellValue(koptelProduct.getValidate());
						}

						Cell cell7 = row.createCell(7);
						cell7.setCellStyle(styles2);
						if (koptelProduct.getKodeKoptelProduct() != null) {
							cell7.setCellValue(koptelProduct
									.getKodeKoptelProduct());
						}

						Cell cell8 = row.createCell(8);
						cell8.setCellStyle(styles2);
						if (koptelProduct.getNamaKoptelProduct() != null) {
							cell8.setCellValue(koptelProduct
									.getNamaKoptelProduct());
						}
					}
				}

				for (int a = 0; a <= 8; a++) {
					sheet2.autoSizeColumn(a);
				}

				String directories = "C:\\DIRECTORY_BILLING_MANAGEMENT\\MergeProduct\\"
						+ years + "\\" + months;

				File files = new File(directories);
				if (!files.exists()) {
					if (files.mkdirs()) {
					}
				}

				String product = "AngsuranTetap";

				String fileName1 = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
						+ years + "/" + months + "/" + product + ".xlsx";
				String fileName2 = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
						+ years + "/" + months + "/" + product + "Detail.xlsx";

				FileOutputStream fos1;
				FileOutputStream fos2;
				try {
					fos1 = new FileOutputStream(fileName1);
					fos2 = new FileOutputStream(fileName2);
					workbook.write(fos1);
					workbook2.write(fos2);
					fos1.close();
					fos2.close();
				} catch (Exception e) {
				}

			}

			if (listAngsuranLainLain.size() != 0) {

				List<KoptelProduct> listAngsuranLainLainFinal = new ArrayList<KoptelProduct>();

				// MERGE WHICH SAME NIK
				for (int i = 0; i < listAngsuranLainLain.size(); i++) {

					String nik = listAngsuranLainLain.get(i).getNik();
					String nama = listAngsuranLainLain.get(i).getNama();
					double jumlah = listAngsuranLainLain.get(i).getJumlah();
					Date begda = listAngsuranLainLain.get(i).getBegda();
					Date enda = listAngsuranLainLain.get(i).getEnda();
					String keterangan = listAngsuranLainLain.get(i)
							.getKeterangan();
					String validate = listAngsuranLainLain.get(i).getValidate();
					String kodeKoptelProducts = "merge";
					String namaKoptelProduct = "merge";

					boolean statusFinal = false;
					int indexFinal = 0;

					for (int j = 0; j < listAngsuranLainLainFinal.size(); j++) {

						if (nik.equals(listAngsuranLainLainFinal.get(j)
								.getNik())) {

							statusFinal = true;
							indexFinal = j;
						}
					}

					if (statusFinal == true) {

						jumlah = jumlah
								+ listAngsuranLainLain.get(indexFinal)
										.getJumlah();

						// UNTUK KOPEG PRODUK

						// BEGDA YG BULANNYA LEBIH BESAR YG DIAMBIL
						if (listAngsuranLainLain.get(indexFinal).getBegda()
								.compareTo(begda) > 0) {
							begda = listAngsuranLainLain.get(indexFinal)
									.getBegda();
						}

						// ENDA YG BULANNYA LEBIH KECIL YG DIAMBIL
						if (listAngsuranLainLain.get(indexFinal).getEnda()
								.compareTo(enda) < 0) {
							enda = listAngsuranLainLain.get(indexFinal)
									.getEnda();
						}

						KoptelProduct koptelProduct = new KoptelProduct(nik,
								nama, jumlah, begda, enda, keterangan,
								validate, kodeKoptelProducts, namaKoptelProduct);

						listAngsuranLainLainFinal
								.set(indexFinal, koptelProduct);

					} else {

						KoptelProduct koptelProduct = new KoptelProduct(nik,
								nama, jumlah, begda, enda, keterangan,
								validate, kodeKoptelProducts, namaKoptelProduct);
						listAngsuranLainLainFinal.add(koptelProduct);
					}
				}

				Workbook workbook = new XSSFWorkbook();
				Sheet sheet = workbook.createSheet("Sheet1");

				// HEADER
				CellStyle style = workbook.createCellStyle();
				style.setFillForegroundColor(IndexedColors.YELLOW.index);
				style.setFillPattern(CellStyle.SOLID_FOREGROUND);
				style.setAlignment(CellStyle.ALIGN_CENTER);
				style.setBorderRight(CellStyle.BORDER_THIN);
				style.setBorderLeft(CellStyle.BORDER_THIN);
				style.setBorderTop(CellStyle.BORDER_THIN);
				style.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont font = (XSSFFont) workbook.createFont();
				font.setBold(true);
				style.setFont(font);

				// DATA
				CellStyle style2 = workbook.createCellStyle();
				style2.setBorderRight(CellStyle.BORDER_THIN);
				style2.setBorderLeft(CellStyle.BORDER_THIN);
				style2.setBorderTop(CellStyle.BORDER_THIN);
				style2.setBorderBottom(CellStyle.BORDER_THIN);

				Iterator<KoptelProduct> iterator = listAngsuranLainLainFinal
						.iterator();

				int rowIndex = 0;
				while (iterator.hasNext()) {

					Row row = sheet.createRow(rowIndex++);

					if (rowIndex == 1) {

						Cell cell0 = row.createCell(0);
						cell0.setCellValue("NIK");
						cell0.setCellStyle(style);

						Cell cell1 = row.createCell(1);
						cell1.setCellValue("NAMA");
						cell1.setCellStyle(style);

						Cell cell2 = row.createCell(2);
						cell2.setCellValue("JUMLAH");
						cell2.setCellStyle(style);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue("BEGDA");
						cell3.setCellStyle(style);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue("ENDA");
						cell4.setCellStyle(style);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue("KETERANGAN");
						cell5.setCellStyle(style);

						Cell cell6 = row.createCell(6);
						cell6.setCellValue("VALIDATE");
						cell6.setCellStyle(style);

						Cell cell7 = row.createCell(7);
						cell7.setCellValue("KODE_KOPTEL_PRODUCT");
						cell7.setCellStyle(style);

						Cell cell8 = row.createCell(8);
						cell8.setCellValue("NAMA_KOPTEL_PRODUCT");
						cell8.setCellStyle(style);
					} else {

						KoptelProduct koptelProduct = iterator.next();

						Cell cell0 = row.createCell(0);
						cell0.setCellStyle(style2);
						if (koptelProduct.getNik() != null) {
							cell0.setCellValue(koptelProduct.getNik());
						}

						Cell cell1 = row.createCell(1);
						cell1.setCellStyle(style2);
						if (koptelProduct.getNama() != null) {
							cell1.setCellValue(koptelProduct.getNama());
						}

						Cell cell2 = row.createCell(2);
						cell2.setCellValue(koptelProduct.getJumlah());
						cell2.setCellStyle(style2);

						Cell cell3 = row.createCell(3);
						cell3.setCellStyle(style2);
						if (koptelProduct.getBegda() != null) {
							cell3.setCellValue(koptelProduct.getBegda());
						}

						Cell cell4 = row.createCell(4);
						cell4.setCellStyle(style2);
						if (koptelProduct.getEnda() != null) {
							cell4.setCellValue(koptelProduct.getEnda());
						}

						Cell cell5 = row.createCell(5);
						cell5.setCellStyle(style2);
						if (koptelProduct.getKeterangan() != null) {
							cell5.setCellValue(koptelProduct.getKeterangan());
						}

						Cell cell6 = row.createCell(6);
						cell6.setCellStyle(style2);
						if (koptelProduct.getValidate() != null) {
							cell6.setCellValue(koptelProduct.getValidate());
						}

						Cell cell7 = row.createCell(7);
						cell7.setCellStyle(style2);
						if (koptelProduct.getKodeKoptelProduct() != null) {
							cell7.setCellValue(koptelProduct
									.getKodeKoptelProduct());
						}

						Cell cell8 = row.createCell(8);
						cell8.setCellStyle(style2);
						if (koptelProduct.getNamaKoptelProduct() != null) {
							cell8.setCellValue(koptelProduct
									.getNamaKoptelProduct());
						}
					}
				}

				for (int a = 0; a <= 8; a++) {
					sheet.autoSizeColumn(a);
				}

				// SUMMARIZE DETAIL
				Workbook workbook2 = new XSSFWorkbook();
				Sheet sheet2 = workbook2.createSheet("Sheet1");

				// HEADER
				CellStyle styles = workbook2.createCellStyle();
				styles.setFillForegroundColor(IndexedColors.YELLOW.index);
				styles.setFillPattern(CellStyle.SOLID_FOREGROUND);
				styles.setAlignment(CellStyle.ALIGN_CENTER);
				styles.setBorderRight(CellStyle.BORDER_THIN);
				styles.setBorderLeft(CellStyle.BORDER_THIN);
				styles.setBorderTop(CellStyle.BORDER_THIN);
				styles.setBorderBottom(CellStyle.BORDER_THIN);

				XSSFFont fonts = (XSSFFont) workbook2.createFont();
				fonts.setBold(true);
				styles.setFont(fonts);

				// DATA
				CellStyle styles2 = workbook2.createCellStyle();
				styles2.setBorderRight(CellStyle.BORDER_THIN);
				styles2.setBorderLeft(CellStyle.BORDER_THIN);
				styles2.setBorderTop(CellStyle.BORDER_THIN);
				styles2.setBorderBottom(CellStyle.BORDER_THIN);

				Iterator<KoptelProduct> iterator2 = listAngsuranLainLain
						.iterator();

				int rowIndex2 = 0;
				while (iterator2.hasNext()) {

					Row row = sheet2.createRow(rowIndex2++);

					if (rowIndex2 == 1) {

						Cell cell0 = row.createCell(0);
						cell0.setCellValue("NIK");
						cell0.setCellStyle(styles);

						Cell cell1 = row.createCell(1);
						cell1.setCellValue("NAMA");
						cell1.setCellStyle(styles);

						Cell cell2 = row.createCell(2);
						cell2.setCellValue("JUMLAH");
						cell2.setCellStyle(styles);

						Cell cell3 = row.createCell(3);
						cell3.setCellValue("BEGDA");
						cell3.setCellStyle(styles);

						Cell cell4 = row.createCell(4);
						cell4.setCellValue("ENDA");
						cell4.setCellStyle(styles);

						Cell cell5 = row.createCell(5);
						cell5.setCellValue("KETERANGAN");
						cell5.setCellStyle(styles);

						Cell cell6 = row.createCell(6);
						cell6.setCellValue("VALIDATE");
						cell6.setCellStyle(styles);

						Cell cell7 = row.createCell(7);
						cell7.setCellValue("KODE_KOPTEL_PRODUCT");
						cell7.setCellStyle(styles);

						Cell cell8 = row.createCell(8);
						cell8.setCellValue("NAMA_KOPTEL_PRODUCT");
						cell8.setCellStyle(styles);

					} else {

						KoptelProduct koptelProduct = iterator2.next();

						Cell cell0 = row.createCell(0);
						cell0.setCellStyle(styles2);
						if (koptelProduct.getNik() != null) {
							cell0.setCellValue(koptelProduct.getNik());
						}

						Cell cell1 = row.createCell(1);
						cell1.setCellStyle(styles2);
						if (koptelProduct.getNama() != null) {
							cell1.setCellValue(koptelProduct.getNama());
						}

						Cell cell2 = row.createCell(2);
						cell2.setCellValue(koptelProduct.getJumlah());
						cell2.setCellStyle(styles2);

						Cell cell3 = row.createCell(3);
						cell3.setCellStyle(styles2);
						if (koptelProduct.getBegda() != null) {
							cell3.setCellValue(koptelProduct.getBegda());
						}

						Cell cell4 = row.createCell(4);
						cell4.setCellStyle(styles2);
						if (koptelProduct.getEnda() != null) {
							cell4.setCellValue(koptelProduct.getEnda());
						}

						Cell cell5 = row.createCell(5);
						cell5.setCellStyle(styles2);
						if (koptelProduct.getKeterangan() != null) {
							cell5.setCellValue(koptelProduct.getKeterangan());
						}

						Cell cell6 = row.createCell(6);
						cell6.setCellStyle(styles2);
						if (koptelProduct.getValidate() != null) {
							cell6.setCellValue(koptelProduct.getValidate());
						}

						Cell cell7 = row.createCell(7);
						cell7.setCellStyle(styles2);
						if (koptelProduct.getKodeKoptelProduct() != null) {
							cell7.setCellValue(koptelProduct
									.getKodeKoptelProduct());
						}

						Cell cell8 = row.createCell(8);
						cell8.setCellStyle(styles2);
						if (koptelProduct.getNamaKoptelProduct() != null) {
							cell8.setCellValue(koptelProduct
									.getNamaKoptelProduct());
						}
					}
				}

				for (int a = 0; a <= 8; a++) {
					sheet2.autoSizeColumn(a);
				}

				String directories = "C:\\DIRECTORY_BILLING_MANAGEMENT\\MergeProduct\\"
						+ years + "\\" + months;

				File files = new File(directories);
				if (!files.exists()) {
					if (files.mkdirs()) {
					}
				}

				String product = "AngsuranLainLain";

				String fileName1 = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
						+ years + "/" + months + "/" + product + ".xlsx";
				String fileName2 = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
						+ years + "/" + months + "/" + product + "Detail.xlsx";

				FileOutputStream fos1;
				FileOutputStream fos2;
				try {
					fos1 = new FileOutputStream(fileName1);
					fos2 = new FileOutputStream(fileName2);
					workbook.write(fos1);
					workbook2.write(fos2);
					fos1.close();
					fos2.close();
				} catch (Exception e) {
				}

			}

			if ((listAngsuranTetap.size() == 0)
					&& (listAngsuranLainLain.size() == 0)) {

				String type = "mergeProduct";
				String status = "failed";
				Date date = new Date();
				path = "-";

				FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
						months, years, status, path, user, date);
				fileStatusDao.saveOrUpdate(fileStatus);

				return "Failed to summarize. NO FILE KOPTEL PRODUCT SUCCESS UPLOAD.";

			} else {

				String zip = "C:\\DIRECTORY_BILLING_MANAGEMENT\\MergeProduct\\"
						+ years + "\\" + months + "-MergeProduct.zip";
				String source = "C:\\DIRECTORY_BILLING_MANAGEMENT\\MergeProduct\\"
						+ years + "\\" + months;
				String status = zipFile(years, months, zip, source);

				if (status.equals("SUCCESS")) {
					String type = "mergeProduct";
					String statuss = "success";
					Date date = new Date();

					String paths = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
							+ years + "/" + months;

					FileStatus fileStatus = new FileStatus(kodeKoptelProduct,
							type, months, years, statuss, paths, user, date);
					fileStatusDao.saveOrUpdate(fileStatus);

					return "SUCCESS SUMMARIZE DATA KOPTEL PRODUCT";
				} else {

					String type = "mergeProduct";
					String statuss = "failed";
					Date date = new Date();
					path = "-";

					FileStatus fileStatus = new FileStatus(kodeKoptelProduct,
							type, months, years, statuss, path, user, date);
					fileStatusDao.saveOrUpdate(fileStatus);

					return "Failed to summarize. ZIP SUMMARIZE FAILED.";
				}
			}

		} catch (Exception e) {

			String type = "mergeProduct";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to summarize. CANNOT ACCESS THE FILE BECAUSE IT IS BEING USED BY ANOTHER PROCESS.");

			return "Failed to summarize. CANNOT ACCESS THE FILE BECAUSE IT IS BEING USED BY ANOTHER PROCESS.";
		}
	}

	public String validationCompareAngsuranTetapProduct(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					String akhirs = null;
					double angsuran = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 4:
									awals = cell.getStringCellValue();
									break;
								case 5:
									akhirs = cell.getStringCellValue();
									break;
								case 8:
									angsuran = cell.getNumericCellValue();
									break;
								}

							} catch (Exception e) {
								System.out
										.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN TETAP PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

								return "false";
							}
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN TETAP PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public String validationCompareAngsuranLainLainProduct(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					double lainnya = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 3:
									awals = cell.getStringCellValue();
									break;
								case 5:
									lainnya = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
								System.out
										.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN LAIN LAIN PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

								return "false";
							}
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA COMPARE ANGSURAN LAIN LAIN IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public List<FileStatus> findCompareProduct(String month, String year) {

		return fileStatusDao.findCompareProduct(month, year);
	}

	public String getDifferenceProduct(String years, String months,
			String path, String kodeKoptelProduct, String user) {

		listDifferenceProductAngsuranTetap = new ArrayList<KoptelProduct>();
		listDifferenceProductAngsuranLainLain = new ArrayList<KoptelProduct>();

		listAllCompareProductAngsuranTetap = new ArrayList<KoptelProduct>();
		listAllCompareProductAngsuranLainLain = new ArrayList<KoptelProduct>();

		List<KoptelProduct> listMergeAngsuranTetap = new ArrayList<KoptelProduct>();
		List<KoptelProduct> listMergeAngsuranLainLain = new ArrayList<KoptelProduct>();

		// ANGSURAN TETAP
		try {

			String[] merge = new String[2];
			merge[0] = "AngsuranTetap.xlsx";
			merge[1] = "AngsuranLainLain.xlsx";

			for (int i = 0; i < 2; i++) {

				String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
						+ years + "/" + months + "/" + merge[i];
				FileInputStream fis = new FileInputStream(fileName);

				Workbook workbook = null;

				if (fileName.toLowerCase().endsWith("xlsx")) {
					workbook = new XSSFWorkbook(fis);
				} else if (fileName.toLowerCase().endsWith("xls")) {
					workbook = new HSSFWorkbook(fis);
				}

				Sheet sheet = workbook.getSheet("Sheet1");

				Iterator<Row> rowIterator = sheet.iterator();
				while (rowIterator.hasNext()) {

					Row row = rowIterator.next();

					if (row.getRowNum() >= 1) {

						String nik = "";
						String nama = "";
						double jumlah = 0.0;
						Date begda = null;
						Date enda = null;
						String keterangan = "";
						String validate = "";
						String kodeKoptelProducts = "";
						String namaKoptelProduct = "";

						Iterator<Cell> cellIterator = row.cellIterator();

						while (cellIterator.hasNext()) {
							Cell cell = cellIterator.next();

							try {
								switch (cell.getColumnIndex()) {
								case 0:
									nik = cell.getStringCellValue();
									break;
								case 1:
									nama = cell.getStringCellValue();
									break;
								case 2:
									jumlah = cell.getNumericCellValue();
									break;
								case 3:
									begda = cell.getDateCellValue();
									break;
								case 4:
									enda = cell.getDateCellValue();
									break;
								case 5:
									keterangan = cell.getStringCellValue();
									break;
								case 6:
									validate = cell.getStringCellValue();
									break;
								case 7:
									kodeKoptelProducts = cell
											.getStringCellValue();
									break;
								case 8:
									namaKoptelProduct = cell
											.getStringCellValue();
									break;
								}
							} catch (Exception e) {
							}

						}// CELL ITERATOR WHILE END

						KoptelProduct koptelProduct = new KoptelProduct(nik,
								nama, jumlah, begda, enda, keterangan,
								validate, kodeKoptelProducts, namaKoptelProduct);

						if (i == 0) {
							listMergeAngsuranTetap.add(koptelProduct);
						} else {
							if (i == 1) {
								listMergeAngsuranLainLain.add(koptelProduct);
							}
						}

					}// IF ROW NUMBER >=1 END
				}// ROW ITERATOR WHILE END

				fis.close();
			}
		} catch (Exception e) {

			String type = "compareProduct";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			System.out
					.println("Failed to compare. FILE SUMMARIZE PRODUCT IS NOT FOUND IN DIRECTORY.");

			return "Failed to compare. FILE SUMMARIZE PRODUCT IS NOT FOUND IN DIRECTORY.";
		}

		File directories = new File(path);
		File[] fList = directories.listFiles();
		String dir = "";

		if (fList.length == 0) {
			dir = "null";
		}

		if (dir.equals("null")) {

			String type = "compareProduct";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			return "Failed to compare. FILE COMPARE PRODUCT DATA IS NOT FOUND IN DIRECTORY.";
		}

		for (File file : fList) {

			String fileName = path + "" + file.getName();

			if (file.getName().startsWith("CompareAngsuranTetapProduct")) {
				checkCompareAngsuranTetapProduct(listMergeAngsuranTetap, years,
						months, fileName, kodeKoptelProduct, user);
			} else {

				if (file.getName().startsWith("CompareAngsuranLainLainProduct")) {
					checkCompareAngsuranLainLainProduct(
							listMergeAngsuranLainLain, years, months, fileName,
							kodeKoptelProduct, user);
				}

			}

		}

		checkDifferenceAllCompareAngsuran(listMergeAngsuranTetap,
				listMergeAngsuranLainLain);

		Workbook workbook = new XSSFWorkbook();
		Sheet sheet = workbook.createSheet("Sheet1");

		// HEADER
		CellStyle style = workbook.createCellStyle();
		style.setFillForegroundColor(IndexedColors.YELLOW.index);
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont font = (XSSFFont) workbook.createFont();
		font.setBold(true);
		style.setFont(font);

		// DATA
		CellStyle style2 = workbook.createCellStyle();
		style2.setBorderRight(CellStyle.BORDER_THIN);
		style2.setBorderLeft(CellStyle.BORDER_THIN);
		style2.setBorderTop(CellStyle.BORDER_THIN);
		style2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<KoptelProduct> iterator = listDifferenceProductAngsuranTetap
				.iterator();

		int rowIndex = 0;
		while (iterator.hasNext()) {

			Row row = sheet.createRow(rowIndex++);

			if (rowIndex == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(style);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("NAMA");
				cell1.setCellStyle(style);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("JUMLAH");
				cell2.setCellStyle(style);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("BEGDA");
				cell3.setCellStyle(style);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("ENDA");
				cell4.setCellStyle(style);

				Cell cell5 = row.createCell(5);
				cell5.setCellValue("KETERANGAN");
				cell5.setCellStyle(style);

				Cell cell6 = row.createCell(6);
				cell6.setCellValue("VALIDATE");
				cell6.setCellStyle(style);

				Cell cell7 = row.createCell(7);
				cell7.setCellValue("KODE_KOPTEL_PRODUCT");
				cell7.setCellStyle(style);

				Cell cell8 = row.createCell(8);
				cell8.setCellValue("NAMA_KOPTEL_PRODUCT");
				cell8.setCellStyle(style);
			} else {

				KoptelProduct koptelProduct = iterator.next();

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(style2);
				if (koptelProduct.getNik() != null) {
					cell0.setCellValue(koptelProduct.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(style2);
				if (koptelProduct.getNama() != null) {
					cell1.setCellValue(koptelProduct.getNama());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellValue(koptelProduct.getJumlah());
				cell2.setCellStyle(style2);

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(style2);
				if (koptelProduct.getBegda() != null) {
					cell3.setCellValue(koptelProduct.getBegda());
				}

				Cell cell4 = row.createCell(4);
				cell4.setCellStyle(style2);
				if (koptelProduct.getEnda() != null) {
					cell4.setCellValue(koptelProduct.getEnda());
				}

				Cell cell5 = row.createCell(5);
				cell5.setCellStyle(style2);
				if (koptelProduct.getKeterangan() != null) {
					cell5.setCellValue(koptelProduct.getKeterangan());
				}

				Cell cell6 = row.createCell(6);
				cell6.setCellStyle(style2);
				if (koptelProduct.getValidate() != null) {
					cell6.setCellValue(koptelProduct.getValidate());
				}

				Cell cell7 = row.createCell(7);
				cell7.setCellStyle(style2);
				if (koptelProduct.getKodeKoptelProduct() != null) {
					cell7.setCellValue(koptelProduct.getKodeKoptelProduct());
				}

				Cell cell8 = row.createCell(8);
				cell8.setCellStyle(style2);
				if (koptelProduct.getNamaKoptelProduct() != null) {
					cell8.setCellValue(koptelProduct.getNamaKoptelProduct());
				}
			}
		}

		for (int a = 0; a <= 8; a++) {
			sheet.autoSizeColumn(a);
		}

		// SUMMARIZE DETAIL
		Workbook workbook2 = new XSSFWorkbook();
		Sheet sheet2 = workbook2.createSheet("Sheet1");

		// HEADER
		CellStyle styles = workbook2.createCellStyle();
		styles.setFillForegroundColor(IndexedColors.YELLOW.index);
		styles.setFillPattern(CellStyle.SOLID_FOREGROUND);
		styles.setAlignment(CellStyle.ALIGN_CENTER);
		styles.setBorderRight(CellStyle.BORDER_THIN);
		styles.setBorderLeft(CellStyle.BORDER_THIN);
		styles.setBorderTop(CellStyle.BORDER_THIN);
		styles.setBorderBottom(CellStyle.BORDER_THIN);

		XSSFFont fonts = (XSSFFont) workbook2.createFont();
		fonts.setBold(true);
		styles.setFont(fonts);

		// DATA
		CellStyle styles2 = workbook2.createCellStyle();
		styles2.setBorderRight(CellStyle.BORDER_THIN);
		styles2.setBorderLeft(CellStyle.BORDER_THIN);
		styles2.setBorderTop(CellStyle.BORDER_THIN);
		styles2.setBorderBottom(CellStyle.BORDER_THIN);

		Iterator<KoptelProduct> iterator2 = listDifferenceProductAngsuranLainLain
				.iterator();

		int rowIndex2 = 0;
		while (iterator2.hasNext()) {

			Row row = sheet2.createRow(rowIndex2++);

			if (rowIndex2 == 1) {

				Cell cell0 = row.createCell(0);
				cell0.setCellValue("NIK");
				cell0.setCellStyle(styles);

				Cell cell1 = row.createCell(1);
				cell1.setCellValue("NAMA");
				cell1.setCellStyle(styles);

				Cell cell2 = row.createCell(2);
				cell2.setCellValue("JUMLAH");
				cell2.setCellStyle(styles);

				Cell cell3 = row.createCell(3);
				cell3.setCellValue("BEGDA");
				cell3.setCellStyle(styles);

				Cell cell4 = row.createCell(4);
				cell4.setCellValue("ENDA");
				cell4.setCellStyle(styles);

				Cell cell5 = row.createCell(5);
				cell5.setCellValue("KETERANGAN");
				cell5.setCellStyle(styles);

				Cell cell6 = row.createCell(6);
				cell6.setCellValue("VALIDATE");
				cell6.setCellStyle(styles);

				Cell cell7 = row.createCell(7);
				cell7.setCellValue("KODE_KOPTEL_PRODUCT");
				cell7.setCellStyle(styles);

				Cell cell8 = row.createCell(8);
				cell8.setCellValue("NAMA_KOPTEL_PRODUCT");
				cell8.setCellStyle(styles);

			} else {

				KoptelProduct koptelProduct = iterator2.next();

				Cell cell0 = row.createCell(0);
				cell0.setCellStyle(styles2);
				if (koptelProduct.getNik() != null) {
					cell0.setCellValue(koptelProduct.getNik());
				}

				Cell cell1 = row.createCell(1);
				cell1.setCellStyle(styles2);
				if (koptelProduct.getNama() != null) {
					cell1.setCellValue(koptelProduct.getNama());
				}

				Cell cell2 = row.createCell(2);
				cell2.setCellValue(koptelProduct.getJumlah());
				cell2.setCellStyle(styles2);

				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(styles2);
				if (koptelProduct.getBegda() != null) {
					cell3.setCellValue(koptelProduct.getBegda());
				}

				Cell cell4 = row.createCell(4);
				cell4.setCellStyle(styles2);
				if (koptelProduct.getEnda() != null) {
					cell4.setCellValue(koptelProduct.getEnda());
				}

				Cell cell5 = row.createCell(5);
				cell5.setCellStyle(styles2);
				if (koptelProduct.getKeterangan() != null) {
					cell5.setCellValue(koptelProduct.getKeterangan());
				}

				Cell cell6 = row.createCell(6);
				cell6.setCellStyle(styles2);
				if (koptelProduct.getValidate() != null) {
					cell6.setCellValue(koptelProduct.getValidate());
				}

				Cell cell7 = row.createCell(7);
				cell7.setCellStyle(styles2);
				if (koptelProduct.getKodeKoptelProduct() != null) {
					cell7.setCellValue(koptelProduct.getKodeKoptelProduct());
				}

				Cell cell8 = row.createCell(8);
				cell8.setCellStyle(styles2);
				if (koptelProduct.getNamaKoptelProduct() != null) {
					cell8.setCellValue(koptelProduct.getNamaKoptelProduct());
				}
			}
		}

		for (int a = 0; a <= 8; a++) {
			sheet2.autoSizeColumn(a);
		}

		String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\CompareProduct\\"
				+ years + "\\" + months + "-DifferenceProduct";

		File files = new File(directory);
		if (!files.exists()) {
			if (files.mkdirs()) {
			}
		}
		String fileName1 = "C:/DIRECTORY_BILLING_MANAGEMENT/CompareProduct/"
				+ years + "/" + months + "-DifferenceProduct" + "/"
				+ "DifferenceAngsuranTetap.xlsx";
		String fileName2 = "C:/DIRECTORY_BILLING_MANAGEMENT/CompareProduct/"
				+ years + "/" + months + "-DifferenceProduct" + "/"
				+ "DifferenceAngsuranLainLain.xlsx";

		FileOutputStream fos1;
		FileOutputStream fos2;
		try {
			fos1 = new FileOutputStream(fileName1);
			fos2 = new FileOutputStream(fileName2);
			workbook.write(fos1);
			workbook2.write(fos2);
			fos1.close();
			fos2.close();
		} catch (Exception e) {
		}

		String zip = "C:\\DIRECTORY_BILLING_MANAGEMENT\\CompareProduct\\"
				+ years + "\\" + months + "-DifferenceProduct.zip";

		String source = "C:\\DIRECTORY_BILLING_MANAGEMENT\\CompareProduct\\"
				+ years + "\\" + months + "-DifferenceProduct";

		String stat = zipFile(years, months, zip, source);

		String type = "compareProduct";
		String statuss = "success";
		Date date = new Date();
		String source2 = "C:/DIRECTORY_BILLING_MANAGEMENT/CompareProduct/"
				+ years + "/" + months + "-DifferenceProduct";

		FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type, months,
				years, statuss, source2, user, date);
		fileStatusDao.saveOrUpdate(fileStatus);

		return "SUCCESS COMPARE PRODUCT DATA";
	}

	public void checkCompareAngsuranTetapProduct(
			List<KoptelProduct> listMergeAngsuranTetap, String years,
			String months, String fileName, String kodeKoptelProduct,
			String user) {

		List<KoptelProduct> listCompareProduct = new ArrayList<KoptelProduct>();

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					String akhirs = null;
					double angsuran = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 4:
									awals = cell.getStringCellValue();
									break;
								case 5:
									akhirs = cell.getStringCellValue();
									break;
								case 8:
									angsuran = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);
					Date akhir = dateParsing(akhirs);

					KoptelProduct koptelProduct = new KoptelProduct(nik, awal,
							akhir, angsuran);
					listCompareProduct.add(koptelProduct);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("CompareAngsuranTetapProduct NOT FOUND.");
		}

		for (int i = 0; i < listCompareProduct.size(); i++) {

			listAllCompareProductAngsuranTetap.add(listCompareProduct.get(i));
			for (int j = 0; j < listMergeAngsuranTetap.size(); j++) {

				if (listCompareProduct.get(i).getNik()
						.equals(listMergeAngsuranTetap.get(j).getNik())) {

					boolean status = true;
					for (int k = 0; k < listDifferenceProductAngsuranTetap
							.size(); k++) {
						if (listDifferenceProductAngsuranTetap.get(k).getNik()
								.equals(listMergeAngsuranTetap.get(j).getNik())) {
							status = false;
						}
					}
					if ((status == true)
							&& ((listCompareProduct.get(i).getJumlah() != listMergeAngsuranTetap
									.get(j).getJumlah())) || (listCompareProduct
											.get(i).getEnda() != listMergeAngsuranTetap.get(j)
											.getEnda())) {
						listDifferenceProductAngsuranTetap
								.add(listMergeAngsuranTetap.get(j));
					}
				}
			}
		}
	}

	public void checkCompareAngsuranLainLainProduct(
			List<KoptelProduct> listMergeAngsuranLainLain, String years,
			String months, String fileName, String kodeKoptelProduct,
			String user) {

		List<KoptelProduct> listCompareProduct = new ArrayList<KoptelProduct>();

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String awals = null;
					double lainnya = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						if (cell.getColumnIndex() >= 1) {
							try {
								switch (cell.getColumnIndex()) {
								case 1:
									int niks = (int) cell.getNumericCellValue();
									nik = "" + niks;
									break;
								case 3:
									awals = cell.getStringCellValue();
									break;
								case 5:
									lainnya = cell.getNumericCellValue();
									break;
								}
							} catch (Exception e) {
							}
						}

					}// CELL ITERATOR WHILE END

					Date awal = dateParsing(awals);

					KoptelProduct koptelProduct = new KoptelProduct(nik, awal,
							lainnya);
					listCompareProduct.add(koptelProduct);

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("CompareAngsuranLainLainProduct NOT FOUND.");
		}

		for (int i = 0; i < listCompareProduct.size(); i++) {

			listAllCompareProductAngsuranLainLain
					.add(listCompareProduct.get(i));
			for (int j = 0; j < listMergeAngsuranLainLain.size(); j++) {

				if (listCompareProduct.get(i).getNik()
						.equals(listMergeAngsuranLainLain.get(j).getNik())) {

					boolean status = true;
					for (int k = 0; k < listDifferenceProductAngsuranLainLain
							.size(); k++) {
						if (listDifferenceProductAngsuranLainLain
								.get(k)
								.getNik()
								.equals(listMergeAngsuranLainLain.get(j)
										.getNik())) {
							status = false;
						}
					}
					if ((status == true)
							&& (listCompareProduct.get(i).getJumlah() != listMergeAngsuranLainLain
									.get(j).getJumlah())) {

						listDifferenceProductAngsuranLainLain
								.add(listMergeAngsuranLainLain.get(j));
					}
				}
			}
		}
	}

	public void checkDifferenceAllCompareAngsuran(
			List<KoptelProduct> listMergeAngsuranTetap,
			List<KoptelProduct> listMergeAngsuranLainLain) {

		boolean status;

		// ANGSURAN TETAP
		for (int i = 0; i < listMergeAngsuranTetap.size(); i++) {

			status = true;
			for (int j = 0; j < listAllCompareProductAngsuranTetap.size(); j++) {

				if (listMergeAngsuranTetap
						.get(i)
						.getNik()
						.equals(listAllCompareProductAngsuranTetap.get(j)
								.getNik())) {
					status = false;
				}
			}

			if (status == true) {
				listDifferenceProductAngsuranTetap.add(listMergeAngsuranTetap
						.get(i));
			}
		}

		// ANGSURAN LAIN LAIN
		for (int i = 0; i < listMergeAngsuranLainLain.size(); i++) {

			status = true;
			for (int j = 0; j < listAllCompareProductAngsuranLainLain.size(); j++) {

				if (listMergeAngsuranLainLain
						.get(i)
						.getNik()
						.equals(listAllCompareProductAngsuranLainLain.get(j)
								.getNik())) {
					status = false;
				}
			}

			if (status == true) {
				listDifferenceProductAngsuranLainLain
						.add(listMergeAngsuranLainLain.get(i));
			}
		}

	}

	public String validationReportAngsuranTetapProduct(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 6:
								jumlah = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
							System.out
									.println("Failed to upload. FORMAT DATA REPORT ANGSURAN TETAP PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

							return "false";
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA REPORT ANGSURAN TETAP PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public String validationReportAngsuranLainLainProduct(String fileName) {

		try {

			FileInputStream fis = new FileInputStream(fileName);
			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;
					Date begda = null;
					Date enda = null;

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 4:
								begda = cell.getDateCellValue();
								break;
							case 5:
								enda = cell.getDateCellValue();
								break;
							case 9:
								jumlah = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
							System.out
									.println("Failed to upload. FORMAT DATA REPORT ANGSURAN LAIN LAIN PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

							return "false";
						}

					}// CELL ITERATOR WHILE END

				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {

			System.out
					.println("Failed to upload. FORMAT DATA REPORT ANGSURAN LAIN LAIN PRODUCT IS NOT MATCH WITH DEFAULT FORMAT.");

			return "false";
		}

		return "true";
	}

	public List<FileStatus> findFinalReportProduct(String month, String year) {

		return fileStatusDao.findFinalReportProduct(month, year);
	}

	public String getSplitFinalReportProduct(String years, String months,
			String path, String kodeKoptelProduct, String user) {

		File directories = new File(path);
		File[] fList = directories.listFiles();
		String dir = "";

		if (fList.length == 0) {
			dir = "null";
		}

		if (dir.equals("null")) {

			String type = "splitProduct";
			String status = "failed";
			Date date = new Date();
			path = "-";

			FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type,
					months, years, status, path, user, date);
			fileStatusDao.saveOrUpdate(fileStatus);

			return "Failed to split. FILE FINAL REPORT PRODUCT IS NOT FOUND IN DIRECTORY.";
		}

		for (File file : fList) {

			listFinalReportProduct = new ArrayList<FinalReportProduct>();
			List<KoptelProduct> listMergeDetailProduct = new ArrayList<KoptelProduct>();
			String report = "";

			String fileName = path + "" + file.getName();

			if (file.getName().startsWith("ReportAngsuranTetapProduct")) {
				getReportAngsuranTetapProduct(years, months, fileName,
						kodeKoptelProduct, user);

				// OPEN LIST MERGE ALL DETAIL
				listMergeDetailProduct = getMergeAngsuranTetapDetail(years,
						months);
				report = "ReportAngsuranTetapProduct";
			} else {
				if (file.getName().startsWith("ReportAngsuranLainLainProduct")) {
					getReportAngsuranLainLainProduct(years, months, fileName,
							kodeKoptelProduct, user);

					// OPEN LIST MERGE ALL DETAIL
					listMergeDetailProduct = getMergeAngsuranLainLainDetail(
							years, months);
					report = "ReportAngsuranLainLainProduct";
				}
			}

			int totalKoptelProduct = listKoptelProductDao
					.getTotalKoptelProduct();

			ArrayList<FinalReportProduct>[] group = (ArrayList<FinalReportProduct>[]) new ArrayList[totalKoptelProduct];

			for (int t = 0; t < totalKoptelProduct; t++) {
				group[t] = new ArrayList<FinalReportProduct>();
			}

			boolean[] flag = new boolean[listMergeDetailProduct.size()];

			for (int b = 0; b < listMergeDetailProduct.size(); b++) {
				flag[b] = true;
			}

			for (int a = 0; a < listFinalReportProduct.size(); a++) {

				double payrollSisa = listFinalReportProduct.get(a).getPayroll();

				int index = -1;
				String kodeKoptelProducts = "";

				String nik = "";
				String nama = "";
				String namaKoptelProduct = "";
				double jumlah = 0.0;
				double payroll = 0.0;
				Date enda = null;
				double selisih = 0.0;
				String keterangan = "";

				int counter = 0;
				List<String> idx = new ArrayList<String>();

				for (int b = 0; b < listMergeDetailProduct.size(); b++) {

					if (listFinalReportProduct.get(a).getNik()
							.equals(listMergeDetailProduct.get(b).getNik())) {
						idx.add("" + b);
						counter++;
					}
				}

				List<KoptelProduct> tempMergeDetailProduct = new ArrayList<KoptelProduct>();

				int counterGlobal = 0;
				boolean loop = true;
				while ((counterGlobal < counter) && (loop == true)) {

					Date begda = new Date();
					begda.setDate(1);
					begda.setMonth(1);
					begda.setYear(10000);

					index = -1;

					for (int c = 0; c < counter; c++) {

						if ((flag[Integer.parseInt(idx.get(c))] == true)
								&& (listMergeDetailProduct.get(
										Integer.parseInt(idx.get(c)))
										.getBegda() == null)) {
							flag[Integer.parseInt(idx.get(c))] = false;
							tempMergeDetailProduct.add(listMergeDetailProduct
									.get(Integer.parseInt(idx.get(c))));
						} else {
							if ((flag[Integer.parseInt(idx.get(c))] == true)
									&& (listMergeDetailProduct
											.get(Integer.parseInt(idx.get(c)))
											.getBegda().compareTo(begda) < 0)) {

								flag[Integer.parseInt(idx.get(c))] = false;
								begda = listMergeDetailProduct.get(
										Integer.parseInt(idx.get(c)))
										.getBegda();
								index = Integer.parseInt(idx.get(c));
							}
						}
					}

					if (index == -1) {

						begda = null;

						for (int aa = 0; aa < tempMergeDetailProduct.size(); aa++) {

							kodeKoptelProducts = tempMergeDetailProduct.get(aa)
									.getKodeKoptelProduct();

							nik = tempMergeDetailProduct.get(aa).getNik();
							nama = tempMergeDetailProduct.get(aa).getNama();
							namaKoptelProduct = tempMergeDetailProduct.get(aa)
									.getNamaKoptelProduct();
							jumlah = tempMergeDetailProduct.get(aa).getJumlah();
							enda = tempMergeDetailProduct.get(aa).getEnda();

							// KOPTEL PRODUCT YG BEGDA NYA TERAKHIR, MAKA
							// PAYROLL DIISI
							// SISANYA
							if (aa == (tempMergeDetailProduct.size() - 1)) {
								payroll = payrollSisa;
							} else {

								if (payrollSisa > jumlah) {
									payroll = jumlah;
									payrollSisa = payrollSisa - payroll;
								} else {
									payroll = payrollSisa;
									payrollSisa = 0.0;
								}
							}

							selisih = jumlah - payroll;

							FinalReportProduct finalReportProduct = new FinalReportProduct(
									nik, nama, jumlah, payroll, selisih, begda,
									enda, keterangan, kodeKoptelProduct,
									namaKoptelProduct);

							int id = listKoptelProductDao
									.getId(kodeKoptelProducts);
							group[id].add(finalReportProduct);
						}

						loop = false;
					}

					else {

						kodeKoptelProducts = listMergeDetailProduct.get(index)
								.getKodeKoptelProduct();

						nik = listMergeDetailProduct.get(index).getNik();
						nama = listMergeDetailProduct.get(index).getNama();
						namaKoptelProduct = listMergeDetailProduct.get(index)
								.getNamaKoptelProduct();
						jumlah = listMergeDetailProduct.get(index).getJumlah();
						enda = listMergeDetailProduct.get(index).getEnda();

						// KOPTEL PRODUCK YG BEGDA NYA TERAKHIR, MAKA PAYROLL
						// DIISI
						// SISANYA
						if (counterGlobal == (counter - 1)) {
							payroll = payrollSisa;
						} else {

							if (payrollSisa > jumlah) {
								payroll = jumlah;
								payrollSisa = payrollSisa - payroll;
							} else {
								payroll = payrollSisa;
								payrollSisa = 0.0;
							}
						}

						selisih = jumlah - payroll;

						FinalReportProduct finalReportProduct = new FinalReportProduct(
								nik, nama, jumlah, payroll, selisih, begda,
								enda, keterangan, kodeKoptelProduct,
								namaKoptelProduct);

						int id = listKoptelProductDao.getId(kodeKoptelProducts);
						group[id].add(finalReportProduct);

						loop = true;
					}

				}
			}

			String directory = "C:\\DIRECTORY_BILLING_MANAGEMENT\\SplitFinalReportProduct\\"
					+ years + "\\" + months;

			File files = new File(directory);
			if (!files.exists()) {
				if (files.mkdirs()) {
				}
			}

			for (int z = 0; z < group.length; z++) {

				if (group[z].size() != 0) {

					Workbook workbook = new XSSFWorkbook();
					Sheet sheet = workbook.createSheet("Sheet1");

					// HEADER
					CellStyle style = workbook.createCellStyle();
					style.setFillForegroundColor(IndexedColors.YELLOW.index);
					style.setFillPattern(CellStyle.SOLID_FOREGROUND);
					style.setAlignment(CellStyle.ALIGN_CENTER);
					style.setBorderRight(CellStyle.BORDER_THIN);
					style.setBorderLeft(CellStyle.BORDER_THIN);
					style.setBorderTop(CellStyle.BORDER_THIN);
					style.setBorderBottom(CellStyle.BORDER_THIN);

					XSSFFont font = (XSSFFont) workbook.createFont();
					font.setBold(true);
					style.setFont(font);

					// DATA
					CellStyle style2 = workbook.createCellStyle();
					style2.setBorderRight(CellStyle.BORDER_THIN);
					style2.setBorderLeft(CellStyle.BORDER_THIN);
					style2.setBorderTop(CellStyle.BORDER_THIN);
					style2.setBorderBottom(CellStyle.BORDER_THIN);

					Row row = sheet.createRow(0);

					Cell cell0 = row.createCell(0);
					cell0.setCellValue("NIK");
					cell0.setCellStyle(style);

					Cell cell1 = row.createCell(1);
					cell1.setCellValue("NAMA");
					cell1.setCellStyle(style);

					Cell cell2 = row.createCell(2);
					cell2.setCellValue("JUMLAH");
					cell2.setCellStyle(style);

					Cell cell3 = row.createCell(3);
					cell3.setCellValue("PAYROLL");
					cell3.setCellStyle(style);

					Cell cell4 = row.createCell(4);
					cell4.setCellValue("SELISIH");
					cell4.setCellStyle(style);

					Iterator<FinalReportProduct> iterator = group[z].iterator();

					int rowIndex = 1;
					while (iterator.hasNext()) {

						FinalReportProduct finalReportProduct = iterator.next();

						Row rows = sheet.createRow(rowIndex++);

						Cell cells0 = rows.createCell(0);
						cells0.setCellStyle(style2);
						if (finalReportProduct.getNik() != null) {
							cells0.setCellValue(finalReportProduct.getNik());
						}

						Cell cells1 = rows.createCell(1);
						cells1.setCellStyle(style2);
						if (finalReportProduct.getNama() != null) {
							cells1.setCellValue(finalReportProduct.getNama());
						}

						Cell cells2 = rows.createCell(2);
						cells2.setCellValue(finalReportProduct.getJumlah());
						cells2.setCellStyle(style2);

						Cell cells3 = rows.createCell(3);
						cells3.setCellValue(finalReportProduct.getPayroll());
						cells3.setCellStyle(style2);

						Cell cells4 = rows.createCell(4);
						cells4.setCellValue(finalReportProduct.getSelisih());
						cells4.setCellStyle(style2);
					}

					for (int a = 0; a <= 15; a++) {
						sheet.autoSizeColumn(a);
					}

					String kodeKoptelProductss = listKoptelProductDao
							.getKodeKoptelProductss(z);

					String directory2 = "C:\\DIRECTORY_BILLING_MANAGEMENT\\SplitFinalReportProduct\\"
							+ years
							+ "\\"
							+ months
							+ "\\"
							+ kodeKoptelProductss;

					File files2 = new File(directory2);
					if (!files2.exists()) {
						if (files2.mkdirs()) {
						}
					}

					String fileNames = "C:/DIRECTORY_BILLING_MANAGEMENT/SplitFinalReportProduct/"
							+ years
							+ "/"
							+ months
							+ "/"
							+ kodeKoptelProductss
							+ "/" + report + ".xlsx";

					FileOutputStream fos;
					try {
						fos = new FileOutputStream(fileNames);
						workbook.write(fos);
						fos.close();
					} catch (Exception e) {
					}

				}
			}
		}

		int totalKoptelProduct = listKoptelProductDao.getTotalKoptelProduct();

		for (int i = 1; i <= totalKoptelProduct; i++) {

			String kodeKoptelProductss = listKoptelProductDao
					.getKodeKoptelProductss(i);

			String zip = "C:\\DIRECTORY_BILLING_MANAGEMENT\\SplitFinalReportProduct\\"
					+ years
					+ "\\"
					+ months
					+ "\\"
					+ kodeKoptelProductss
					+ "-FinalReportProduct.zip";
			String source = "C:\\DIRECTORY_BILLING_MANAGEMENT\\SplitFinalReportProduct\\"
					+ years + "\\" + months + "\\" + kodeKoptelProductss;

			String stat = zipFile(years, months, zip, source);
		}

		String type = "splitProduct";
		String statuss = "success";
		Date date = new Date();
		String directory2 = "C:/DIRECTORY_BILLING_MANAGEMENT/SplitFinalReportProduct/"
				+ years + "/" + months + "/";

		FileStatus fileStatus = new FileStatus(kodeKoptelProduct, type, months,
				years, statuss, directory2, user, date);
		fileStatusDao.saveOrUpdate(fileStatus);

		return "SUCCESS SPLIT FINAL REPORT PRODUCT";
	}

	public void getReportAngsuranTetapProduct(String years, String months,
			String fileName, String kodeKoptelProduct, String user) {

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;
					double payroll = 0.0;
					double selisih = 0.0;
					Date begda = null;
					Date enda = null;
					String keterangan = "";
					String kodeKoptelProducts = "";
					String namaKoptelProduct = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 6:
								payroll = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					boolean status = true;
					int index = 0;
					for (int k = 0; k < listFinalReportProduct.size(); k++) {
						if (listFinalReportProduct.get(k).getNik().equals(nik)) {
							status = false;
							index = k;
						}
					}
					if (status == false) {
						listFinalReportProduct.get(index).setPayroll(payroll);
					} else {
						FinalReportProduct finalReportProduct = new FinalReportProduct(
								nik, nama, jumlah, payroll, selisih, begda,
								enda, keterangan, kodeKoptelProducts,
								namaKoptelProduct);
						listFinalReportProduct.add(finalReportProduct);
					}
				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("ReportAngsuranTetapProduct NOT FOUND.");
		}

	}

	public void getReportAngsuranLainLainProduct(String years, String months,
			String fileName, String kodeKoptelProduct, String user) {

		try {

			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 6) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;
					double payroll = 0.0;
					double selisih = 0.0;
					Date begda = null;
					Date enda = null;
					String keterangan = "";
					String kodeKoptelProducts = "";
					String namaKoptelProduct = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 1:
								int niks = (int) cell.getNumericCellValue();
								nik = "" + niks;
								break;
							case 2:
								nama = cell.getStringCellValue();
								break;
							case 4:
								begda = cell.getDateCellValue();
								break;
							case 5:
								enda = cell.getDateCellValue();
								break;
							case 9:
								payroll = cell.getNumericCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					boolean status = true;
					int index = 0;
					for (int k = 0; k < listFinalReportProduct.size(); k++) {
						if (listFinalReportProduct.get(k).getNik().equals(nik)) {
							status = false;
							index = k;
						}
					}
					if (status == false) {
						listFinalReportProduct.get(index).setPayroll(payroll);
					} else {
						FinalReportProduct finalReportProduct = new FinalReportProduct(
								nik, nama, jumlah, payroll, selisih, begda,
								enda, keterangan, kodeKoptelProducts,
								namaKoptelProduct);
						listFinalReportProduct.add(finalReportProduct);
					}
				}// IF ROW NUMBER >=6 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
			System.out.println("ReportAngsuranLainLainProduct NOT FOUND.");
		}

	}

	public List<KoptelProduct> getMergeAngsuranTetapDetail(String years,
			String months) {

		List<KoptelProduct> listMerge = new ArrayList<KoptelProduct>();

		try {

			String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
					+ years + "/" + months + "/" + "AngsuranTetapDetail.xlsx";
			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;
					Date begda = null;
					Date enda = null;
					String keterangan = "";
					String validate = "";
					String kodeKoptelProducts = "";
					String namaKoptelProduct = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 2:
								jumlah = cell.getNumericCellValue();
								break;
							case 3:
								begda = cell.getDateCellValue();
								break;
							case 4:
								enda = cell.getDateCellValue();
								break;
							case 5:
								keterangan = cell.getStringCellValue();
								break;
							case 6:
								validate = cell.getStringCellValue();
								break;
							case 7:
								kodeKoptelProducts = cell.getStringCellValue();
								break;
							case 8:
								namaKoptelProduct = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					KoptelProduct koptelProduct = new KoptelProduct(nik, nama,
							jumlah, begda, enda, keterangan, validate,
							kodeKoptelProducts, namaKoptelProduct);
					listMerge.add(koptelProduct);

				}// IF ROW NUMBER >=1 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
		}

		return listMerge;
	}

	public List<KoptelProduct> getMergeAngsuranLainLainDetail(String years,
			String months) {

		List<KoptelProduct> listMerge = new ArrayList<KoptelProduct>();

		try {

			String fileName = "C:/DIRECTORY_BILLING_MANAGEMENT/MergeProduct/"
					+ years + "/" + months + "/"
					+ "AngsuranLainLainDetail.xlsx";
			FileInputStream fis = new FileInputStream(fileName);

			Workbook workbook = null;

			if (fileName.toLowerCase().endsWith("xlsx")) {
				workbook = new XSSFWorkbook(fis);
			} else if (fileName.toLowerCase().endsWith("xls")) {
				workbook = new HSSFWorkbook(fis);
			}

			Sheet sheet = workbook.getSheet("Sheet1");

			Iterator<Row> rowIterator = sheet.iterator();
			while (rowIterator.hasNext()) {

				Row row = rowIterator.next();

				if (row.getRowNum() >= 1) {

					String nik = "";
					String nama = "";
					double jumlah = 0.0;
					Date begda = null;
					Date enda = null;
					String keterangan = "";
					String validate = "";
					String kodeKoptelProducts = "";
					String namaKoptelProduct = "";

					Iterator<Cell> cellIterator = row.cellIterator();

					while (cellIterator.hasNext()) {
						Cell cell = cellIterator.next();

						try {
							switch (cell.getColumnIndex()) {
							case 0:
								nik = cell.getStringCellValue();
								break;
							case 1:
								nama = cell.getStringCellValue();
								break;
							case 2:
								jumlah = cell.getNumericCellValue();
								break;
							case 3:
								begda = cell.getDateCellValue();
								break;
							case 4:
								enda = cell.getDateCellValue();
								break;
							case 5:
								keterangan = cell.getStringCellValue();
								break;
							case 6:
								validate = cell.getStringCellValue();
								break;
							case 7:
								kodeKoptelProducts = cell.getStringCellValue();
								break;
							case 8:
								namaKoptelProduct = cell.getStringCellValue();
								break;
							}
						} catch (Exception e) {
						}

					}// CELL ITERATOR WHILE END

					KoptelProduct koptelProduct = new KoptelProduct(nik, nama,
							jumlah, begda, enda, keterangan, validate,
							kodeKoptelProducts, namaKoptelProduct);
					listMerge.add(koptelProduct);

				}// IF ROW NUMBER >=1 END
			}// ROW ITERATOR WHILE END

			fis.close();

		} catch (Exception e) {
		}

		return listMerge;
	}

}
